Sum all corresponding values from multiple sheets

Hi Jeremy,

I don't see any way for a function to determine the number of sheets in a document. If you were willing to add a column, and you have at least as many rows in that table as sheets then this formula

=INDIRECT("Sheet "&ROW(cell)−1&"::Table 1::A1")

would direct you to A1 in sheet 1::table 1 if the function is in row 2. In row 3 it would retreive the value in Sheet 2. It will throw errors if the is no sheet n (more rows than sheets). in order to get your SUM to work you would need IFERROR()

=IFERROR(INDIRECT("Sheet "&ROW()−1&"::Table 1::A1"),0)

this will place a 0 if there is an error.

You could then sum your column and have that result where ever you want. Hide the column.

A script could be written that would update your formula.

Or you could update your formula with a click each time you add a sheet. Might be the easiest.

quinn

Hi Jeremy,

Nice solution by quinn that showed me how to use the INDIRECT function. Thanks, quinn 🙂.

Just having a play with Numbers, I thought about Sheet names and unique Table names. If a Table has a unique name within a document, Numbers does not require the Sheet name.

A Sheet named Accounts for the Accounts Department (the Sheet name is not important because it is not used in any formulas). Each employee in Accounts has their own unique table.

(I have used 10% for Federal, an imaginary %)

Two Header Rows and one Footer Row.

Another Sheet named Sales for the Sales Department (again the Sheet name is not important).

Summary Sheet (and the Sheet name is not important):

Formula in D2

='Smith, John'::B9

(the Footer Row for Smith, John). No need for a Sheet name as 'Smith, John' is a unique Table in the document.

Formula in D3

='Smith, Jane'::B9

Yes, you have to point and click to get each reference. This is best done with all the Tables on one Sheet. Then Cut the tables and Paste them to the relevant Department Sheet. The formulas will automatically adjust.

Formula in D6

=SUM(D)

Formula in D7

=SUMIF($A,$A7,D)

(the sum of all Wages in the Accounts Department).

Formula in D8

=SUMIF($A,$A8,D)

(the sum of all Wages in the Sales Department).

CheckSum in D9

=D7+D8

Conditional highlighting in red because it does not equal D6

Fill Right to get formulas for Federal.

The Boing! in red (conditional highlighting because it does not equal OK) tells us that Brown, Anne has been listed as Admin, not Sales (as per her personal Table).

Formula in B2 (and Fill Down)

=IF(A2='Smith, John'::B1,"OK","Boing!")

Compares the Department in A2 with the Department in the unique Table 'Smith, John'::B1

OK, else Boing!

Oh well, I had fun playing with this 🙂.

Regards,

Ian

Hi Jeremy,

Glad it got your started. I like having a list of everybody anyway.

Ian's observation that unique table names don't need a sheet name is a good one. At the least it can simplify your formula. It also would allow you to keep many tables on one sheet as he suggests. I think the big advantage to this would be in navigation. If you have more than a handful of employees, scrolling thru sheet tabs will get tiresome. Navigating to a specific table in a sheet can be done with the little triangle on the sheet tab. If you are not wanting to print each employee's table separately it might speed your workflow.

quinn

Hi Jeremy,

The 'charm' of Numbers (a term that I am stealing from Jerry (Jerrold Green1) happens here:

Jane's table

Add another row to Jane's table (9 Feb 2014)

The Footer Row automatically updates the Sum.

The Summary table (on Sheet Whatever) updates automatically because it refers to the Footer Row of Jane's Table.

The Charm of Numbers 🙂.

Regards,

Ian.

I found a great shortcut for 'referencing' a cell in multiple sheets. 🙂

I have over 60 sheets... and going to every sheet and clicking on the cell was a struggle and time consuming. Then I tried changing the cell manually and it was worst.

So I decided to copy the 'formula' in text format, paste it on 'Pages' and use the 'Find Replace' function. Just indicate the 'cell' you want to replace. Copy the edited formula, go back to Numbers and paste it.