Sum Multiple sheets based on sheet name

[Adamzurick]

Hi there,

I have a model that is identical across 30 sheets. Each sheet rolls up to 1 of 3 different buckets. Using a mapping table of each tab name and the bucket it relates to, I'd like to summarize the 3 buckets into 3 separate tabs (identical to the model that's being used in the 30 sheets) that sums the cells of its child's sheets.

What formula would be the best to accomplish this? I imagine its some sort of INDIRECT but the one problem i'm forseeing is that if I add or remove tabs later (to both the workbook and mapping) the formulas i've come across aren't dynamic enough to address this type of change.

Below is the best i've come across so far. Is there anything i'm missing that might be better? Thanks ahead of time for your help!!!


SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))

 

[Oeldere]

did you consider adding all data on 1 worksheet (e.g. with a macro). After that you can use pivot table to analyse the data.

 

[Adamzurick]

did you consider adding all data on 1 worksheet (e.g. with a macro). After that you can use pivot table to analyse the data.

I thought of that too, but there just seems like there would be a more elegant way through a formula, right?

 

[Aladin Akyurek]

What is the range to which the name sheets refers?

 

[FDibbins]

If you keep the new sheets INSIDE the "sheets" range (I assume its a range), then the formula should update with any new sheets you add. When I need to do anything like this, I sometimes add a new sheet at the beginning and call it Start, and another at the end, called (duh) End. Then if your sheet named range starts with Start and ends with End, you can drag sheets into and out of that range to your hearts content, and the formulas should work just fine

 

[Adamzurick]

If you keep the new sheets INSIDE the "sheets" range (I assume its a range), then the formula should update with any new sheets you add. When I need to do anything like this, I sometimes add a new sheet at the beginning and call it Start, and another at the end, called (duh) End. Then if your sheet named range starts with Start and ends with End, you can drag sheets into and out of that range to your hearts content, and the formulas should work just fine

This Start and End Solution is perfect for what i'm doing. Thanks for the help!

 

[FDibbins]

Great stuff, happy to help