Excel Formula

  • Thread starter Thread starter Glenn
  • Start date Start date
G

Glenn

Looking for formula to put in cell that will add up cell
C10 from 14 different sheets within a workbook.
Thanks
 
Glenn said:
Looking for formula to put in cell that will add up cell
C10 from 14 different sheets within a workbook.
Thanks
Click to expand...

If the worksheets (i.e. the worksheet tabs) are one after the other,
continuously, and you will not move them in the future, you can use
something like
=SUM(Sheet1:Sheet14!C10)
Otherwise, you need to spell it out like
=Sheet1!C10 + Sheet4!C10+ Sheet6!C10..........
 
Thanks but still not working.
Basically I have 14 sheets running one after each other
and the last sheet (Sheet 15) is the totals sheet. I have
renamed all the other sheets the first one is called Pat
Pettett and the last is called Sue Fisher. I need to put
a formula in a cell on sheet 15 that will add up cell C10
from the 14 sheet previously starting with Pat Pettett and
ending with Sue Fisher. I have tried the formula below
but is not working. I need a formula for the range that
way when i insert a sheet the formula will change to
include the new sheet

Thanks.
 
Use dummy sheets and always add sheets in-between them,
insert a new sheet first and another last (that is put it before the summary
sheet), rename them to
First and Last

now use this formula in the summary sheet

=SUM(First:Last!C14)

after you have done that you can hide those 2 sheets from view if you want.
 
As you have renamed the worksheets, you need to use the new names in the
formula. Also, as you have spaces in the names, you need to put the
worksheet name range inside single quotes. So the formula you need is:
=SUM('Pat Pettett:Sue Fisher'!C10)

Now, if you insert a new sheet BETWEEN 'Pat Pettett' and 'Sue Fisher' it
will be included in the SUM. However, if you insert the new sheet before
'Pat Pettett' or after 'Sue Fisher', it will not be inside the range and
will therefore not be included in the SUM. Also, you must not delete or move
either of these worksheets, or things will go wrong.

What's often done in such circumstances is to have two blank sheets, called
maybe 'Start' and 'Finish', which you never change, and you use the formula
=SUM(Start:Finish!C10)
Your 14 sheets would be between these two sheets. Adding/deleting/moving
worksheets in between 'Start' and 'Finish' then works properly.
 
Back
Top