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