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.