Sum the same cell across multiple sheets - variable number of sheets

[ROBJ]

Good Day,

I have a typical set of month sheets (labelled: Jan Total... Feb Total... etc.) and a "YTD" sheet. All month sheets have identical layouts. The YTD sheet also has an identical layout.

Before each of the month sheets I have to create a variable (dynamic) number of sheets from a template (identical layout to the month sheets) that need to have their data consolidated in the monthly sheets.

i.e. before Jan Total I will have 4 sheets named HM1, HM2, HM3, HM1; before February I will have 3 sheets HM5, HM6, HM7, etc. Another user will have the same workbook but different number of sheets (named differently) before the monthly total, i.e HD27, HD28, HD29, HD30, HD31, HD35 before January...

What would be the easiest way to define for each month from which sheets to look up the data from?

Can the formulas like SUM(HM1:HM4!A1) be made dynamic in the monthly sheets, and change based on the sheets it looks up the data from?

Thanking you in advance for your insight.

Rob

[Admin]

Hi Rob,

Welcome to board !

First question why you use different sheet for different month ? Don't you use a single sheet for all data by adding a column for month ? which will make your analysis easy.

Can you attach a sample workbook with expected results ?

[ROBJ]

Hello :)

I tried to upload the file but is larger than 102kb and it would not allow me.
For an easy analogy, the WS containing the data that need to be summed in the monthly WS are the weeks of the year. My code is HM so I would have WS named HM1, HM2, HM 3, HM 4 for January. Another month I might need to divide the week into 2 (having 2 WS for one week), for example HM15, HM16, .....HM23 to be summed in June. Some months might need to combine 2 weeks into 1, then I would have only 2 sheets data to be added in the Monthly WS. What I need is to be able to tell on the monthly sheet the name & number of the sheets that predate the month and have a formula like SUM(HM1:HM4!A1) become dynamic based on these parameter[s].

Thanks

Rob

[Admin]

you don't need to upload all the data. keep first few rows of data and delete rest. wanted to see the format of each sheet.

[ROBJ]

Hello :)

Please see attached. I might have answered my own question by adding a "helper" WS before the 1st & last week of the month for formulas to update. " =SUM('JAN START:JAN END'!C5) "
Not the most elegant solution, but it works.
I am still in dire need of help with some of the VBA code for this WB. Do I need to start a new thread or just post the code on this one?
Again, very much appreciate your help.

Thanks

Rob

[Admin]

Hello :)

Please see attached. I might have answered my own question by adding a "helper" WS before the 1st & last week of the month for formulas to update. " =SUM('JAN START:JAN END'!C5) "
Not the most elegant solution, but it works.

Thanks

Rob

It's an elegant solution ! Usually we propose this way to handle 3d formulas.

[ROBJ]

Good Day,

How can the INDIRECT formula be used for the formula in cell C5 ( =SUM('JAN START:JAN END'!C5) ) to update automatically based on a drop down menu in cell A1 which allows to chose the month?
i.e. I would like the monthly sheet for February to update the 3D formulas automatically after choosing FEB from the drop down?

Appreciate your help.

[Admin]

See http://www.xl-central.com/blog.html

[snb]

Don't split data into separate worksheets.
Use an intelligent table and a pivottable instead.