[John Linker]

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

For example, Id 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 doesnt 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($A 3="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)


--
Gary''s Student - gsnu200792


"John Linker" wrote:

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

For example, Id 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 doesnt 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($A 3="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?

[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" <John wrote in message
...
I cant seem to find a way to change a cell reference without using IF
statements.

For example, Id 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 doesnt 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($A 3="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?

[John Linker[_2_]]

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

"Gary''s Student" wrote:

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)


--
Gary''s Student - gsnu200792


"John Linker" wrote:

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

For example, Id 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 doesnt 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($A 3="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?