Sheeloo
I tried the formula below and it gave me a #ref error in the cell, but I
think you are on the right track. Any other ideas?

"Sheeloo" wrote:

Try
=sum(indirect("worksheet1:" & n20 & "!L20") )

"excelhurtsme" wrote:

I am sorry I wasn't clear enough, the result of the formula I want to enter
into the sum formula is a worksheet name but not the first worksheet. I am
trying to come up with a way to sum up a certain cell across multiple
worksheets but with the worksheets not created yet. I am building a daily
report template that includes man hours on a job, the man hours need to be
totalled on a daily basis. As each worksheet is created it needs to include
itself and all previous worksheets into the total. Building dummy sheets
before and after gives a running total but changes the total to date values
of the sheets before the last to include all sheets. I need the formula to
update itself every time a worksheet is created.
=SUM(worksheet1:worksheet2!L20)
=SUM(worksheet1:worksheet3!L20)
=SUM(worksheet1:worksheet4!L20) and so on as sheets are built

I have included in a hidden cell the formula that puts the worksheet name
into that cell and it updates itself beautifully as new worksheets are
created. I just want to know if the result of that cell can somehow be
entered as the end worksheet in the sum formula?

Any help would be appreciated!

"Chip Pearson" wrote:

I'm assuming that Sheet1!N20 contains the name of a worksheet from
which you want to get the value in L20. You can use the INDIRECT
function to do this:

=SUM(INDIRECT(Sheet1!N20&"!L20"))

Here, INDIRECT takes the sheet name from Sheet1!N20, appends "L20" to
it, and returns the SUM. So, if Sheet1!N20 contains the string
"Sheet3" (sans quotes), the formula is the same as

=SUM(Sheet3!L20)

You can use INDIRECT to convert any arbitrary text string into an
actual range reference that can be used with other functions.


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 11 Dec 2008 12:26:01 -0800, exchequers
wrote:

Is this possible? =sum(worksheet1:(RESULT OF N20)!L20)
with N20 having the formula to display the worksheet name in it?
not sure how to put in the (RESULT OF N20) into the formula if it is even
possible.