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?