How to calculate percentage acroos sheets in a workbook

[Guest]

I have 58 sheets in a workbook, and I need to calculate the percentage across
all sheets into sheet 1. We are using this workbook to conduct a training
analysis. Each sheet represents a learner, and their knowledge of the
content as high medium or low. Sheet 1 should give us an overview of the
specs, yet it seems excel 2000 cuts you off after 35 sheets when writing the
formula

 

[Guest]

If the values are in the same cell across all worksheets you can group your
worksheets by clicking on the sheet 1 and shift + Click on sheet 58. And use
something like the formula below to calc your average.

=SUM(Sheet1:Sheet58!A1)/58

 

[Guest]

Thanks It worked just fine!

If the values are in the same cell across all worksheets you can group your
worksheets by clicking on the sheet 1 and shift + Click on sheet 58. And use
something like the formula below to calc your average.

=SUM(Sheet1:Sheet58!A1)/58

 

[Guest]

Thanks!
I am newbie to formulas...excel is not usually my job! I appreciate the
time taken to help someone wo has never ever witten a formula.

Thanks again

Kristian (tosh105)

 

[Dave Peterson]

I'm not sure what your formula looks like, but maybe you could use multiple
cells.

One cell for the first 30 worksheets another cell for the last 28 and a third
cell to add them (if that's what you're doing???).

Alternatively, if your data is laid out nicely (and depending on what you're
doing), you may be able to use this technique.

Say you wanted to add all the values in A1 of all those 58 sheets...

I'd create two new worksheets--one to the far right and one to the far left.

Call them Start and End

Then using a sheet (Summary) that is outside this "sandwich" of worksheets:

=Sum(start:end!A1)

Then you can drag sheets in and out of that sandwich to play what if games.

I'd put a couple of notes on each of these sheets:

"don't delete this sheet!"

And protect the worksheets so that people don't use it for real data.

========
One of the real good reasons to layout your data nicely--I like to put my totals
in Row 1 to make it even easier.

 

[Dave Peterson]

Sorry. I didn't see the other responses.

I'm not sure what your formula looks like, but maybe you could use multiple
cells.

One cell for the first 30 worksheets another cell for the last 28 and a third
cell to add them (if that's what you're doing???).

Alternatively, if your data is laid out nicely (and depending on what you're
doing), you may be able to use this technique.

Say you wanted to add all the values in A1 of all those 58 sheets...

I'd create two new worksheets--one to the far right and one to the far left.

Call them Start and End

Then using a sheet (Summary) that is outside this "sandwich" of worksheets:

=Sum(start:end!A1)

Then you can drag sheets in and out of that sandwich to play what if games.

I'd put a couple of notes on each of these sheets:

"don't delete this sheet!"

And protect the worksheets so that people don't use it for real data.

========
One of the real good reasons to layout your data nicely--I like to put my totals
in Row 1 to make it even easier.