Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable sheet name in formula
Hello,
I dont know if this is possible, but I am trying to build a formula which contains the name of a sheet that will change every day. In fact, I would like a cell to contain the name of the sheet and then reference the content of that cell in my formula. Ex: sheet2.A3 would contain the text "Sheet2" my formula would look like = sheet2.A3!B4*C45........., where sheet2.A3!B4 would be cell B4 of sheet2. Thank you. Eric. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable sheet name in formula
Hi
use INDIRECT for this. -- Regards Frank Kabel Frankfurt, Germany "Eric" schrieb im Newsbeitrag ... Hello, I dont know if this is possible, but I am trying to build a formula which contains the name of a sheet that will change every day. In fact, I would like a cell to contain the name of the sheet and then reference the content of that cell in my formula. Ex: sheet2.A3 would contain the text "Sheet2" my formula would look like = sheet2.A3!B4*C45........., where sheet2.A3!B4 would be cell B4 of sheet2. Thank you. Eric. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable sheet name in formula
Frank,
INDIRECT seems to work when you want to reference a cell but not for just the sheet name. I want to be able to create a variable that contains the name of a sheet and then use that variable in a formula. Thank you. Eric. "Frank Kabel" wrote in message ... Hi use INDIRECT for this. -- Regards Frank Kabel Frankfurt, Germany "Eric" schrieb im Newsbeitrag ... Hello, I dont know if this is possible, but I am trying to build a formula which contains the name of a sheet that will change every day. In fact, I would like a cell to contain the name of the sheet and then reference the content of that cell in my formula. Ex: sheet2.A3 would contain the text "Sheet2" my formula would look like = sheet2.A3!B4*C45........., where sheet2.A3!B4 would be cell B4 of sheet2. Thank you. Eric. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable sheet name in formula
Hi
it will work also for the sheet name. e.g. A1: sheet1 A2: X1 A3: Formula =INDIRECT("'" & A1 & "'!" & A2) this will get the value from sheet1!X1 -- Regards Frank Kabel Frankfurt, Germany "Eric" schrieb im Newsbeitrag ... Frank, INDIRECT seems to work when you want to reference a cell but not for just the sheet name. I want to be able to create a variable that contains the name of a sheet and then use that variable in a formula. Thank you. Eric. "Frank Kabel" wrote in message ... Hi use INDIRECT for this. -- Regards Frank Kabel Frankfurt, Germany "Eric" schrieb im Newsbeitrag ... Hello, I dont know if this is possible, but I am trying to build a formula which contains the name of a sheet that will change every day. In fact, I would like a cell to contain the name of the sheet and then reference the content of that cell in my formula. Ex: sheet2.A3 would contain the text "Sheet2" my formula would look like = sheet2.A3!B4*C45........., where sheet2.A3!B4 would be cell B4 of sheet2. Thank you. Eric. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable sheet name in formula
You are right! It's working...
Thank you very much. Eric. "Frank Kabel" wrote in message ... Hi it will work also for the sheet name. e.g. A1: sheet1 A2: X1 A3: Formula =INDIRECT("'" & A1 & "'!" & A2) this will get the value from sheet1!X1 -- Regards Frank Kabel Frankfurt, Germany "Eric" schrieb im Newsbeitrag ... Frank, INDIRECT seems to work when you want to reference a cell but not for just the sheet name. I want to be able to create a variable that contains the name of a sheet and then use that variable in a formula. Thank you. Eric. "Frank Kabel" wrote in message ... Hi use INDIRECT for this. -- Regards Frank Kabel Frankfurt, Germany "Eric" schrieb im Newsbeitrag ... Hello, I dont know if this is possible, but I am trying to build a formula which contains the name of a sheet that will change every day. In fact, I would like a cell to contain the name of the sheet and then reference the content of that cell in my formula. Ex: sheet2.A3 would contain the text "Sheet2" my formula would look like = sheet2.A3!B4*C45........., where sheet2.A3!B4 would be cell B4 of sheet2. Thank you. Eric. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable sheet name in formula
Eric,
If the cell will always be B4, as implied in your original post (you seem to have made up your own syntax for it <g), you need not store it in a cell. Changing Frank's solution slightly: =INDIRECT("'" & A1 & "'!" & "B4") The quote marks around B4 are necessary, or else it will look in B4 for a cell address. In the year 2028, Excel will give us an INDIRECT function that will allow us to just supply a sheet name without having to drum up all those hieroglyphics. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Eric" wrote in message ... You are right! It's working... Thank you very much. Eric. "Frank Kabel" wrote in message ... Hi it will work also for the sheet name. e.g. A1: sheet1 A2: X1 A3: Formula =INDIRECT("'" & A1 & "'!" & A2) this will get the value from sheet1!X1 -- Regards Frank Kabel Frankfurt, Germany "Eric" schrieb im Newsbeitrag ... Frank, INDIRECT seems to work when you want to reference a cell but not for just the sheet name. I want to be able to create a variable that contains the name of a sheet and then use that variable in a formula. Thank you. Eric. "Frank Kabel" wrote in message ... Hi use INDIRECT for this. -- Regards Frank Kabel Frankfurt, Germany "Eric" schrieb im Newsbeitrag ... Hello, I dont know if this is possible, but I am trying to build a formula which contains the name of a sheet that will change every day. In fact, I would like a cell to contain the name of the sheet and then reference the content of that cell in my formula. Ex: sheet2.A3 would contain the text "Sheet2" my formula would look like = sheet2.A3!B4*C45........., where sheet2.A3!B4 would be cell B4 of sheet2. Thank you. Eric. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable sheet name in formula
Earl, thank you for your input.
B4 was just an example. In fact the cell reference varies too, it is the result of a specific formula... Eric. "Earl Kiosterud" wrote in message ... Eric, If the cell will always be B4, as implied in your original post (you seem to have made up your own syntax for it <g), you need not store it in a cell. Changing Frank's solution slightly: =INDIRECT("'" & A1 & "'!" & "B4") The quote marks around B4 are necessary, or else it will look in B4 for a cell address. In the year 2028, Excel will give us an INDIRECT function that will allow us to just supply a sheet name without having to drum up all those hieroglyphics. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Eric" wrote in message ... You are right! It's working... Thank you very much. Eric. "Frank Kabel" wrote in message ... Hi it will work also for the sheet name. e.g. A1: sheet1 A2: X1 A3: Formula =INDIRECT("'" & A1 & "'!" & A2) this will get the value from sheet1!X1 -- Regards Frank Kabel Frankfurt, Germany "Eric" schrieb im Newsbeitrag ... Frank, INDIRECT seems to work when you want to reference a cell but not for just the sheet name. I want to be able to create a variable that contains the name of a sheet and then use that variable in a formula. Thank you. Eric. "Frank Kabel" wrote in message ... Hi use INDIRECT for this. -- Regards Frank Kabel Frankfurt, Germany "Eric" schrieb im Newsbeitrag ... Hello, I dont know if this is possible, but I am trying to build a formula which contains the name of a sheet that will change every day. In fact, I would like a cell to contain the name of the sheet and then reference the content of that cell in my formula. Ex: sheet2.A3 would contain the text "Sheet2" my formula would look like = sheet2.A3!B4*C45........., where sheet2.A3!B4 would be cell B4 of sheet2. Thank you. Eric. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How To make a sheet reference Variable (eq: sum(sheet!D2:H2)) | Excel Discussion (Misc queries) | |||
Variable sheet and Formula Sheet | Excel Discussion (Misc queries) | |||
variable in a link where the variable is the name of the sheet | Excel Worksheet Functions | |||
Variable Sheet Name in Formula | Excel Discussion (Misc queries) | |||
Concatentate a formula with a variable sheet name. | Excel Programming |