Use Cell Value Sheet Reference for sum function

[sdunla2]

I'm trying to write a formula to sum numbers from different sheets. The formula will be used to total values from different report sheets, but the last report number is not known.

I have already set up macro to rename each sheet "REPORT X" where X is the number of the report. On the totals sheet I will have cell A1 as the total number of reports and cell B1 will sum up the operating hours on all reports.

How can I use the formula =SUM('REPORT X:REPORT 1'!C32:D32) and have it use the value of cell A1 for X in the above formula?

 

[sdunla2]

bump