Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using contents of a cell as the sheet name in another formula
I am trying to use the contents of a cell as the sheet name in another formula.
A B 1 Res A ='Res A'!C4 2 Res B ='Res B'!C4 Right now, 'Res A' and 'Res B' in column B are hard coded. I want them to refer to the text in column A so when I copy the formulas down, the references are updated automatically instead of having to do it manually. Once we make that work, the equation I really need to embed the variable sheet names in is a little more complicated, and looks like this: B C 4 Res A =INDEX('Res A'!$A$1:$AZ$256, MATCH($A$1,'Res A'!$A$1:$A$256,), MATCH(C$1,'Res A'!$A$1:$AZ$1,)) Where all 3 instances of 'Res A' need to be replaced by a variable expression. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using contents of a cell as the sheet name in another formula
Instead of:
='Res A'!C4 use =Indirect(A1 & "!C4") You can change the content of A1 and the sheet reference will also change. -- Gary''s Student - gsnu200803 "sbird" wrote: I am trying to use the contents of a cell as the sheet name in another formula. A B 1 Res A ='Res A'!C4 2 Res B ='Res B'!C4 Right now, 'Res A' and 'Res B' in column B are hard coded. I want them to refer to the text in column A so when I copy the formulas down, the references are updated automatically instead of having to do it manually. Once we make that work, the equation I really need to embed the variable sheet names in is a little more complicated, and looks like this: B C 4 Res A =INDEX('Res A'!$A$1:$AZ$256, MATCH($A$1,'Res A'!$A$1:$A$256,), MATCH(C$1,'Res A'!$A$1:$AZ$1,)) Where all 3 instances of 'Res A' need to be replaced by a variable expression. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using contents of a cell as the sheet name in another formula
Since you have spaces in the worksheet name, you could use:
=indirect("'" & a1 & "'!c4") sbird wrote: I am trying to use the contents of a cell as the sheet name in another formula. A B 1 Res A ='Res A'!C4 2 Res B ='Res B'!C4 Right now, 'Res A' and 'Res B' in column B are hard coded. I want them to refer to the text in column A so when I copy the formulas down, the references are updated automatically instead of having to do it manually. Once we make that work, the equation I really need to embed the variable sheet names in is a little more complicated, and looks like this: B C 4 Res A =INDEX('Res A'!$A$1:$AZ$256, MATCH($A$1,'Res A'!$A$1:$A$256,), MATCH(C$1,'Res A'!$A$1:$AZ$1,)) Where all 3 instances of 'Res A' need to be replaced by a variable expression. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
transfer contents from a cell in previous sheet if the sheet is a | New Users to Excel | |||
Returning contents of a cell in another sheet in same workbook | Excel Discussion (Misc queries) | |||
Displaying cell contents on one sheet | Excel Discussion (Misc queries) | |||
Lookup cell contents in on sheet based on a formula in second sheet | Excel Worksheet Functions | |||
Cell contents vs. Formula contents | Excel Discussion (Misc queries) |