refence worksheet name into a formula

[excelhurtsme]

I am trying to write a formula that includes a reference to the worksheet
name. The formula I am using is =SUM('Day 1ay 2'!L20) but in the place of
Day 2 I want the name of the worksheet to appear.

 

[T. Valko]

The formula I am using is =SUM('Day 1ay 2'!L20)

but in the place of Day 2 I want the name of the worksheet to appear.

Day 2 is the name of the worksheet!

Do you mean that you want to enter the sheet name in a cell and have the
formula use that cell reference?

If so, are your sheet names really Day 1, Day 2 etc?

 

[excelhurtsme]

Yes, exactly......as new days are added and named (day 1, day 2 etc...just an
example) the sum formula needs to work from the first worksheet (day 1) to
the last added worksheet.

 

[T. Valko]

Try this:

Sheet names are Day 1, Day 2, Day 3, etc.

A1 = 3

=SUMPRODUCT(SUMIF(INDIRECT("'Day
"&ROW(INDIRECT("1:"&A1))&"'!L20"),"<1E100"))

That is the same as:

=SUM('Day 1ay 3'!L20)

If A1 is empty you'll get a #REF! error.

If you enter a number in A1 and that sheet doesn't exist you'll get a #REF!
error.

 

[T. Valko]

Note that in the formula there is a space character between Day and "&ROW:

....Day "&ROW...

Sometimes line wrap will break at those space characters and will make it
look like there is no space in the formula.

 

[RagDyeR]

You can make your Sum() formula dynamic, where it totals all the sheets in
the WB,
including any new ones.

Insert a "dummy" WS at the beginning of your sheet tabs (extreme left) and
name it
Start
Then insert another at the extreme right and name it
End.

Now, change your formula to:
=Sum(Start:End!L20)

Insert every new sheet *between* the "sandwich" of those 2 dummy sheets, and
your formula will *automatically* include those new sheets in the total.

You can even play "What If", by moving sheets in and out of the sandwich, to
see different totals in different scenarios.

Also, if you wish, you can hide the dummy sheets and the formulas will still
work.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Yes, exactly......as new days are added and named (day 1, day 2 etc...just
an
example) the sum formula needs to work from the first worksheet (day 1) to
the last added worksheet.