Cells across multiple sheets

  • Thread starter Thread starter Deborah
  • Start date Start date
D

Deborah

I'm doing a baseball stat sheet. I have a Total page that
sums cells from each individual Game1, Game2 etc.
worksheet. I don't want to have to modify the Total
worksheet formulas every time I add another Game
worksheet. Is there some way to sum worksheets using a
common prefix (=sum(Game&!D17)) or some other function I
can use? Thanks.
 
Deborah,

Insert two blank dummy sheets,a nd use them as the first and last sheet
refeenced in the sum function. Then, any new sheets inserted between them
will be automatically included in the sum function.

HTH,
Bernie
MS Excel MVP
 
Deborah:

You can use a macro with a loop that iterates through all
sheets, updating the Totals fields for each sheet.

I don't have time to code it right now, but here are some
hints:

Iterate through all sheets in the active workbook
For each sheet in ActiveWorkbook
'Do all sheets except the Totals sheet
If sheet.name <> "Totals" (whatever the actual name is)
'Do the calculation updates for the current sheet
End For

This is very cryptic, but I hope it helps!

Blessings in abundance, all the best, and ENJOY!

Art Du Rea Carlisle, PA USA
 
Bernie,
Thanks, but I'm an Excel rookie and I must be doing
something wrong with my formula. In my totals page I had
=sum(start!a17+game1!a17+game2!a17+....+end!a17). When I
added a new sheet it didn't get included into the formula
& totals. I also tried =sum(start!a17:end!a17). Help!
What am I doing wrong.
Deborah
 
Deborah,

Sorry, I should have been more specific. By using shift-tab-clicks to
select multiple sheets, you would end up with this formula:

=SUM(Start:End!A17)

That is is the 3D style that auto-updates.

HTH,
Bernie
MS Excel MVP
 
Thank you
-----Original Message-----
Deborah:

You can use a macro with a loop that iterates through all
sheets, updating the Totals fields for each sheet.

I don't have time to code it right now, but here are some
hints:

Iterate through all sheets in the active workbook
For each sheet in ActiveWorkbook
'Do all sheets except the Totals sheet
If sheet.name <> "Totals" (whatever the actual name is)
'Do the calculation updates for the current sheet
End For

This is very cryptic, but I hope it helps!

Blessings in abundance, all the best, and ENJOY!

Art Du Rea Carlisle, PA USA

.
Click to expand...
 
Thank you. That did just what I needed.
-D
-----Original Message-----
Deborah,

Insert two blank dummy sheets,a nd use them as the first and last sheet
refeenced in the sum function. Then, any new sheets inserted between them
will be automatically included in the sum function.

HTH,
Bernie
MS Excel MVP




.
Click to expand...
 
Back
Top