TechArena Community Best way to summarise a varying quantity of worksheets ?

 Tags:

# Best way to summarise a varying quantity of worksheets ?

## Windows Software

#1
27-11-2008
 Member Join Date: Nov 2008 Posts: 24
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.

#2
27-11-2008
 Member Join Date: May 2008 Posts: 4,809
Re: Best way to summarise a varying quantity of worksheets ?

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.
#3
27-11-2008
 Member Join Date: Apr 2008 Posts: 2,139
Re: Best way to summarise a varying quantity of worksheets ?

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

 Similar Threads for: "Best way to summarise a varying quantity of worksheets ?" Thread Thread Starter Forum Replies Last Post In Zynga adventure world the maximum supplies and gold quantity is less Palomi Video Games 4 23-11-2011 11:08 AM GPS Accuracy of Motorola Motoactv is varying Valan Portable Devices 7 15-11-2011 02:34 PM Homefront: Quality over Quantity Y-Maker Video Games 5 27-03-2011 10:36 PM Simulate the varying conditions Aamin Software Development 3 26-02-2009 10:18 AM Quantity of RAM and Virtual Memory in your PC Johnny Tips & Tweaks 0 27-08-2008 02:31 PM

All times are GMT +5.5. The time now is 03:08 PM.