Best way to summarise a varying quantity of worksheets ?

Hi friends, i would like to know the best way to summaries a varying quantity of worksheets .I have monthly workbooks which each have 5 weekly sheets and one summary sheet. I intend to copy the summary sheets to a new "total period analysis" workbook.

In this second annual workbook I intend to total all the monthly sheets together then analyse it. BUT as the books for the months to come haven't been written yet, I can't include them in my formulas. How can I write formulas to include the sheets that WILL be included without getting #ref errors? Can it be done. Or, is there a better way to do this? Some VBA to add the new sheets to the formulas maybe? I don't know VBA.

Thanks in advance for any help.

Hi, the best way is to have Dummy sheets with an obvious identifier, perhaps "DUMMY" in cell A1.

The main summary sheet will than have something to reference, and a simple IF(sheet2!A1="DUMMY",...) to avoid erroneous calculations.

Remember blank cells will generally be treated as zeros in numerical calculations.

The proper data can be pasted over the dummys as it becomes available.

Check this link http://www.bettersolutions.com/excel...N620422111.htm.

You can 'bookend' the weekly sheets in the monthly workbook with a pair of (perhaps hidden) blank sheets, named, say Left and Right,

Then a formula might be =SUM(Left:Right!A1), which will sum Left, Right, and whatever sheets are in between