Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use cell contents to build sheet reference
I have a worksheet that lists other worksheet names in column A. In column B,
I want to retrieve a value from the sheet listed in column A. For example, if the value in A1 is "MySheet" (without the quotes), then B1 should return the value of 'MySheet'!M20. I tried building the formula: ="='"&CELL("contents",A1)&"'!M20" But the cell just shows the formula I built as text, instead of the actual formula. Thanks for any help, ~ Horatio |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use cell contents to build sheet reference
=A1&"'!M20"
"Horatio J. Bilge, Jr." wrote: I have a worksheet that lists other worksheet names in column A. In column B, I want to retrieve a value from the sheet listed in column A. For example, if the value in A1 is "MySheet" (without the quotes), then B1 should return the value of 'MySheet'!M20. I tried building the formula: ="='"&CELL("contents",A1)&"'!M20" But the cell just shows the formula I built as text, instead of the actual formula. Thanks for any help, ~ Horatio |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use cell contents to build sheet reference
rather ="'"&A1&"'!M20"
"Horatio J. Bilge, Jr." wrote: I have a worksheet that lists other worksheet names in column A. In column B, I want to retrieve a value from the sheet listed in column A. For example, if the value in A1 is "MySheet" (without the quotes), then B1 should return the value of 'MySheet'!M20. I tried building the formula: ="='"&CELL("contents",A1)&"'!M20" But the cell just shows the formula I built as text, instead of the actual formula. Thanks for any help, ~ Horatio |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use cell contents to build sheet reference
Hi,
Try this =INDIRECT(A1&"!M20") Mike "Horatio J. Bilge, Jr." wrote: I have a worksheet that lists other worksheet names in column A. In column B, I want to retrieve a value from the sheet listed in column A. For example, if the value in A1 is "MySheet" (without the quotes), then B1 should return the value of 'MySheet'!M20. I tried building the formula: ="='"&CELL("contents",A1)&"'!M20" But the cell just shows the formula I built as text, instead of the actual formula. Thanks for any help, ~ Horatio |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use cell contents to build sheet reference
Try this...
="'"&A1&"'"&"'!M20" If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "Horatio J. Bilge, Jr." wrote: I have a worksheet that lists other worksheet names in column A. In column B, I want to retrieve a value from the sheet listed in column A. For example, if the value in A1 is "MySheet" (without the quotes), then B1 should return the value of 'MySheet'!M20. I tried building the formula: ="='"&CELL("contents",A1)&"'!M20" But the cell just shows the formula I built as text, instead of the actual formula. Thanks for any help, ~ Horatio |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use cell contents to build sheet reference
That still doesn't return the value, but just builds a text string.
A1 says MySheet. B1 says 'MySheet'!M20 instead of 25 (the value that is in 'MySheet'!M20). ~ Horatio "cm" wrote: rather ="'"&A1&"'!M20" "Horatio J. Bilge, Jr." wrote: I have a worksheet that lists other worksheet names in column A. In column B, I want to retrieve a value from the sheet listed in column A. For example, if the value in A1 is "MySheet" (without the quotes), then B1 should return the value of 'MySheet'!M20. I tried building the formula: ="='"&CELL("contents",A1)&"'!M20" But the cell just shows the formula I built as text, instead of the actual formula. Thanks for any help, ~ Horatio |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use cell contents to build sheet reference
That works great! Thank you.
~ Horatio "Mike H" wrote: Hi, Try this =INDIRECT(A1&"!M20") Mike "Horatio J. Bilge, Jr." wrote: I have a worksheet that lists other worksheet names in column A. In column B, I want to retrieve a value from the sheet listed in column A. For example, if the value in A1 is "MySheet" (without the quotes), then B1 should return the value of 'MySheet'!M20. I tried building the formula: ="='"&CELL("contents",A1)&"'!M20" But the cell just shows the formula I built as text, instead of the actual formula. Thanks for any help, ~ Horatio |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cell contents from a reference | Excel Discussion (Misc queries) | |||
Build a reference to another worksheet from cell values in currentsheet | Excel Worksheet Functions | |||
Using cell value to build external reference | Excel Discussion (Misc queries) | |||
Using a cells contents to reference a sheet. | Excel Worksheet Functions | |||
Getting contents of a cell when cell reference is in the sheet | Excel Discussion (Misc queries) |