How To make a sheet reference Variable (eq: sum(sheet!D2:H2))

[John Linker]

I cant seem to find a way to change a cell reference without using IF
statements.

For example, I’d like to change the target worksheet the SUM formula
references depending on the value of a certain cell.

An example of a formula I would like to use is

=SUM(‘A3’!D2:H2)

Where as A3 equals the name of a certain employee. So then the formula would
go to that employee's specific worksheet and reference D2:H2.

Problem is excel doesn’t allow things to be so simple from what I gather… so
I end up with a formula like this:

=IF($A3="Christian",SUM(Christian!$D$2:$H$2),IF($A3="Muniz",SUM(Muniz!$D$2:$H$2),IF($A3="Natal",SUM(Natal!$D$2:$H$2),IF($A3="Quigley",SUM(Quigley!$D$2:$H$2),IF($A3="Washington",SUM(Washington!$D$2:$H$2))))))

I even tried to use the concatenate forumal to work around my issue, but it
didnt work (haha)

=CONCATENATE("=sum(",A3,"!","D2:H2)") becomes =sum(Christian!D2:H2) but as a
text only value


Does anyone know of a way to make the first formula to work the way I want
it to?

 

[Gary''s Student]

INDIRECT() can help you:

I cell A3 contains:
Ravenswood
and this is the name of a worksheet, then:

=SUM(INDIRECT(A3 & "!D2:H2"))

will give you the same as

=SUM(Ravenswood!D2:H2)

 

[Chip Pearson]

John,

Use the INDIRECT function. E.g.,

=SUM(INDIRECT("'"&A3&"'!D2:H2"))


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

 

[John Linker]

Thanks for the quick reply. I actually tried the Indirect formula once but I
didnt format it correctly apparently Thanks for the example.