Sum Cells on Non-Contiguous Sheets

[Claymationator]

I have a workbook that I am in the process of creating that will be used for planning/budgeting for a large number of stores. Each sheet will have an individual store on it where assumptions will be built in for the next year's plan. Each store has different build years, states, managers, region directors, etc. and I want to be able to aggregate at any of those levels (i.e. what is the total sales for stores built in 2010, or in NV?)

I know that I can get a total aggregate amount by selecting the first to the last sheet, but that will only work for the overall total...and if I choose a state, I need to be able to only add up those stores that fall in a particular state...same thing for region directors, etc (non-contiguous sheets.)

I want to be able to set it up so that I can choose the aggregate level from a dropdown in a summary sheet instead of having to create a vast number of sheets in the workbook that has the data already aggregated at every level.

One thought I had would be to put in a few variables into each sheet that highlights which groupings the particular store falls into. For example, a store in NV could have the following groupings listed in B1:B3

Build Year = 2010
Region Director = #3
State = NV

So, if I were to select 2010, #3, or NV that sheet would show up in the aggregated summary. However, if I were to choose a grouping other than one of those 3 it wouldn't show up in the total.

Basically the aggregate levels that I need won't always be on contiguous sheets, so I need to devise a formula or VBA that will allow me to get there.

Any help is appreciated.

 

[Smitty]

I just did something similar and created a master worksheet that aggregated all of the individual worksheet data into transactional items (like you'd have in a database), then used Pivot Tables.

It took some VBA to aggregate it all, but not much, and once you have that you're golden.

HTH,

 

[Claymationator]

Thanks for the post Smitty. I have done that as well in the past, but I am no expert in VBA, and wondered about a formula method. I was able to come up with a solution to anyone that is interested.

I created a dynamic named range called SheetList that would return all of the matching sheets to aggregate based on the user selection, and then used this formula to return the sum of the applicable cells from all the sheets in the SheetList:

=SUMPRODUCT((N(INDIRECT("'"&SheetList&"'!"&ADDRESS(ROW(),COLUMN())))))

Since my summary sheet is set up the exact same way as the other individual sheets this worked well to get me the desired result.

 

[Marcelo Branco]

Thank you. This helped me alot!

Regards,

M.