Hi, would somebody be so kind to help.
I have a numbers document which will eventually have multiple sheets, sheets will be exactly the same representing job summaries as I will duplicate as jobs come in.
I would like to have a main summary sheet totalling data from jobs sheets as they’re added.
I have wondered if somehow an indirect function would do this but can’t get my head around it.
Any help would be greatly appreciated.
Unfortunately there is no way to make a "range" reference to the same cell across multiple sheets like you can with Excel.
If you are looking to sum up a relatively small number of cells across a number of sheets, one way is to create a new table that draws in the data from those sheets so it is all in one table. Below is an example.
Row 1 and column A are headers. Last row is a footer.
Formula in cell B2 =IFERROR(INDIRECT($A2&"::"&B$1),"")
Fill down and across to complete the table
Formula in B16 = SUM(B)
As you can see, I currently have only 3 data sheets. The rest of the sheet names in the table are for future use. Those rows will fill in as you create new sheets with those names. At least that is what is supposed to happen. I believe I recall a recent bug where it wouldn't always update the summary table as it should but it seems to be working now as best I can tell.
This can be used to aggregate as many cells as you want from the sheets but it takes one column per cell so it gets a little unwieldy after a while.
Unfortunately there is no way to make a "range" reference to the same cell across multiple sheets like you can with Excel.
If you are looking to sum up a relatively small number of cells across a number of sheets, one way is to create a new table that draws in the data from those sheets so it is all in one table. Below is an example.
Row 1 and column A are headers. Last row is a footer.
Formula in cell B2 =IFERROR(INDIRECT($A2&"::"&B$1),"")
Fill down and across to complete the table
Formula in B16 = SUM(B)
As you can see, I currently have only 3 data sheets. The rest of the sheet names in the table are for future use. Those rows will fill in as you create new sheets with those names. At least that is what is supposed to happen. I believe I recall a recent bug where it wouldn't always update the summary table as it should but it seems to be working now as best I can tell.
This can be used to aggregate as many cells as you want from the sheets but it takes one column per cell so it gets a little unwieldy after a while.
Thank you so much.
Took me the last couple of hours to get my head around your solution but it works a treat. Thanks again
Summary of multiple sheets formula
