Best way to summarise a varying quantity of worksheets ?

[WindowsUser]

Sponsored Links

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.

[Macarenas]

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.

[chroma]

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