Consolidating worksheets

[EvansB2]

Hi,

I have a number of worksheets that are all identical in terms of format, structure, etc. They are templates and have numbers in them. I have created a summer sheet so I can sum all the numbers in to totals. This summary shee is simply one of the templates but blank.

I there a easier way to sum the values instead of physicaly adding and linking the cells? I know doing it this way won' take long as I can simply drag the formula down and across. But I remember that I came across a more sophisticated way that also didn't involve running a macro in VBA.

Any assistance would be gratefully recieed.

Regards

Ben

 

[Rijnsent]

Hi Ben,
depending on your structure this might work:
=SUM(Apr:Jun!B6) -> this should sum up all cells B6 in the sheets Apr, ???, Jun
This formula assumes:
-the first sheet is named "Apr", the last "Jun"
-the number of sheets in between is not fixed (so one sheet named "May" is logical, but you can have multiple)
-it would add up all the cells B6 in those sheets
-if your sheet names have spaces or are numbers, you might need [] or '' around the sheet names, e.g. =SUM(‘(9)27)’!D7)

Hope that helps,
Koen