refence worksheet name into a formula
 

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

refence worksheet name into a formula
 

The formula I am using is =SUM('Day 1:Day 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?


--
Biff
Microsoft Excel MVP


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

refence worksheet name into a formula
 

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" wrote:

The formula I am using is =SUM('Day 1:Day 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?


--
Biff
Microsoft Excel MVP


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

refence worksheet name into a formula
 

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 1:Day 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.


--
Biff
Microsoft Excel MVP


"excelhurtsme" wrote in message
...
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" wrote:

The formula I am using is =SUM('Day 1:Day 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?


--
Biff
Microsoft Excel MVP


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

refence worksheet name into a formula
 

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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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 1:Day 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.


--
Biff
Microsoft Excel MVP


"excelhurtsme" wrote in message
...
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" wrote:

The formula I am using is =SUM('Day 1:Day 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?


--
Biff
Microsoft Excel MVP


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

refence worksheet name into a formula
 

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!
================================================== ===

"excelhurtsme" wrote in message
...
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" wrote:

The formula I am using is =SUM('Day 1:Day 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?


--
Biff
Microsoft Excel MVP


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