Adding Through Multiple Sheets

  • Thread starter Thread starter Tony Kenck
  • Start date Start date
T

Tony Kenck

I am summing a series of sheets in the "Z" direction, e.g. I want to add
cells B2 on the worksheets that are sitting between Sheet4 and Sheet9.

The formula looks something like:
=SUM(Sheet1:Sheet9!B2)

Now what I want to do is a test to see if a sheet is actualy physically
located between sheet4 and sheet9. I was thinking of putting a 1 in the
cell C1 on each of those sheets so then if I were to put in an equation
something like:
=if(SUM(currSheet:Sheet9)=1,1,0) it would let me know if that sheet is
siting between the sheets in question. (Where currSheet is the name of
the tab on which the current formula resides.)

My question is, how do I extract the name of the current tab to put into
the equation. I think I would use the cell "filename" command to extract
the name of the tab, but I do not know how to get that into the SUM
euation above. I appreciate any suggestions.

Thanks,
Tony
 
Hi Roger,

Thanks for the quick response. That's actually what I am doing, but I am
trying to take it to one more step of complexity--I want to add from the
current sheet to Last.

So many of the sheets would have the exact same formula, except instead
of being a constant First, I am looking for a variable First that
references the sheet on which the formula appears.

Sorry if I wasn't clear in the first messge. I hope this clrifies my
question.

Cheers,
Tony Kenck


Roger Govier said:
Hi Tony

When I am doing this sort of thing, I usually creat a sheet called First and
one called Last.
I drag these sheets to the positions required to give the range of sheets I
wish to enclose.
Then the generic formula
=SUM(First:Last!yourcell) can be used in any calculations you require.

Once the sheets are in position, you can Hide the sheets if required,
without affecting the formula.
Click to expand...
 
Hi Tony

When I am doing this sort of thing, I usually creat a sheet called First and
one called Last.
I drag these sheets to the positions required to give the range of sheets I
wish to enclose.
Then the generic formula
=SUM(First:Last!yourcell) can be used in any calculations you require.

Once the sheets are in position, you can Hide the sheets if required,
without affecting the formula.
 
Back
Top