|
How To make a sheet reference Variable (eq: sum(sheet!D2:H2))
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? |
How To make a sheet reference Variable (eq: sum(sheet!D2:H2))
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? |
How To make a sheet reference Variable (eq: sum(sheet!D2:H2))
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? |
| All times are GMT +1. The time now is 07:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com