Copying Numerical Totals of separate worksheets to a single Summary Worksheet

[buster1831]

I wonder if anyone can please help me.



I have a several workbooks containing 50 to 80 worksheets. Each workbook has
a summary worksheet of the numerical totals of each worksheets. Each
worksheet is named with a persons name e.g. SmithC, BlackR, HoffmanD.



I'm having to at the moment to copy the totals of each worksheet to the
summary worksheet, one by one. As you can imagine this takes time.



Is there a quicker way to do this?



Thanks,



Gerry

 

[Jenny]

If it's formatted the way I am imagining, then all you need to do is use the
following formula.

For instance, if each total on each sheet is in cell C40, then on the
summary sheet in the cell use the formula =SmithC!C40

The down side is you must do this 50 times. I am unsure how to make the
fill differentiate the various worksheets. I hope this is what you were
after.

Jenny

 

[Gord Dibben]

Gerry

If the total on each sheet is in the same cell on each sheet you can 3D
reference.

Add a new sheet after the Summary sheet which I assume is first in book.

Name this sheet Start.

Add a new sheet at end of book and name it End

On Summary sheet =SUM(Start:End!H34)

If not in same cell, lotsa work<g>

You could perhaps group the sheets and manipulate rows and columns so's you
get the total on each sheet in the same cell?

Anything that would distinguish the placement of the total cell on each sheet?


Gord Dibben Excel MVP