entering function results into another function

[excelhurtsme]

Is this possible? =sum(worksheet1RESULT 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.

 

[Sheeloo]

Try
=SUM(INDIRECT("'" & N20 & "'!L20"))

basically construct a string which gives you the address and pass that to
INDIRECT...
Since you have only one cell L20 then why are you using SUM? If you just
need the value in L20 then use
INDIRECT("'" & N20 & "'!L20")

Also if worksheet name does not have spaces then you can use
=INDIRECT(N20 & "!L20")

 

[Luke M]

Sheeloo,

OP is trying to sum across multiple sheets, not just the one value.

 

[Chip Pearson]

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)

 

[Luke M]

Chip,
Your formula is only looking at one sheet. OP wants to sum across multiple
sheets.
I'm thinking
=SUM(INDIRECT("Sheet1:"&N20 & "!L20"))
should work, but I can not quite get it to. Hopefully someone else can
elaborate, or figure out what I'm missing.

 

[Bernard Liengme]

If you just wanted the value in L20 from the sheet named in N20 you would
use
=INDIRECT(N2&"!L20")

So you might think you could use =SUM(INDIRECT("Sheet1:"&N20&"!L20")) but
INDIRECT does not support 3D references.

Do the sheet names form a nice series like Sheet1, Sheet2, ....Sheet10? If
so there is a nice trick shown in
http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/e5816df8027ba006


By the way: The phase I think you wanted was "Value RETURNED by formula in
N20"

best wishes

 

[excelhurtsme]

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!

 

[Luke M]

If INDIRECT does not support 3D references, that explains why I could not get
my earlier idea to work. Sorry excelhurtsme.

 

[Sheeloo]

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

 

[excelhurtsme]

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]

See Bernard's response...