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

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

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

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

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

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

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