sum a large number of worksheets

[Michael]

Hi, I need to set up a worksheet that will summarise the data in the other
worksheets in the workbook.

Each worksheet has a different name eg Area N2, Area S2 etc.

I know this is easy but I could end up with over 100 worksheets that the
formula has to add up and so setting up the first formula will take ages.

Is there a quick way of setting the formula up or do I just have to get on
with it?

Thank you in advance of any replies

 

[Michael Dobony]

Hi, I need to set up a worksheet that will summarise the data in the other
worksheets in the workbook.

Each worksheet has a different name eg Area N2, Area S2 etc.

I know this is easy but I could end up with over 100 worksheets that the
formula has to add up and so setting up the first formula will take ages.

Is there a quick way of setting the formula up or do I just have to get on
with it?

Thank you in advance of any replies

Set up a sum cell in each worksheet in the same location (i.e. a:2000),
highlight all the worksheets, and name the cell range, sum the range.

 

[Michael]

Thanks,

I'm trying to add up cell A1 on every sheet and put the answer in cell a1 on
the summary sheet.

So I have selected all of the worksheets and selected cell A1 and selected
Insert-Name-define but it only shows the range as being the active sheet even
though all of the sheets are grouped.

Am i doing something wrong?

 

[Pete_UK]

You can set up two (blank) worksheets, one called start the other
called end, and position them so that they form a "sandwich"
encompassing the sheets you want to sum from (i.e. your summary sheet
will be outside this "sandwich".

Then you can use a formula like:

=SUM(start:end!A1)

to add up all the A1 cells within that "sandwich". You can move sheets
into or out of this "sandwich" to model the effects.

Hope this helps.

Pete

 

[Michael]

Thanks, much appreciated

You can set up two (blank) worksheets, one called start the other
called end, and position them so that they form a "sandwich"
encompassing the sheets you want to sum from (i.e. your summary sheet
will be outside this "sandwich".

Then you can use a formula like:

=SUM(start:end!A1)

to add up all the A1 cells within that "sandwich". You can move sheets
into or out of this "sandwich" to model the effects.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete