ROBJ
12-03-2017, 11:00 AM
Hello there,
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 sheet) that need to have their data consolidated in the monthly sheets. i.e. JAN TOTAL will have 4 sheets named HD1, HD2, HD3, HD4; before FEB TOTAL I will have 3 sheets named HM5, HM6, HM7, etc.
I added a "helper" WS before the 1st & last sheet corresponding to the month to handle 3d formulas... i.e. " C5 =SUM('JAN START:JAN END'!C5) "
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 would like the monthly sheet for FEB TOTAL to update the 3D formulas automatically after choosing FEB from the drop down?
Appreciate your help.
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 sheet) that need to have their data consolidated in the monthly sheets. i.e. JAN TOTAL will have 4 sheets named HD1, HD2, HD3, HD4; before FEB TOTAL I will have 3 sheets named HM5, HM6, HM7, etc.
I added a "helper" WS before the 1st & last sheet corresponding to the month to handle 3d formulas... i.e. " C5 =SUM('JAN START:JAN END'!C5) "
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 would like the monthly sheet for FEB TOTAL to update the 3D formulas automatically after choosing FEB from the drop down?
Appreciate your help.