How to substitute reference to worksheet name in a formula with value in another cell

Posted by Ferdinand on February 08, 2002 7:42 AM

Hi. Hopefully somebody can help me on this annoying problem I have:

My file has two sheets (sheet1 and sheet2)

In cell A1 on sheet1 I refer to a value in cell B2 on sheet2. The formula in A1 is therefore =sheet2!B2.

My question is, how can I substitute the reference to sheet2 with a value in a cell on sheet1, lets say cell A2?

In the given example I would have in cell A2 the value sheet2. I tried the following formulas for A1 to get the same result as above, but none of them work:

=A2!B2
=A2&!B2
=A2!B2
=A2!B2
etc.

Any help is greatly appreciated!

Posted by Derek on February 08, 2002 8:52 AM

Re: =INDIRECT(A2) (NT)

Posted by Derek on February 08, 2002 8:56 AM

Re: =INDIRECT(A2) Entry in A2 is Sheet2!B2 (NT)

Posted by Ferdinand on February 08, 2002 9:13 AM

Thanks, but the entry in A2 is only the name of the sheet, i.e. Sheet2

Posted by Aladin Akyurek on February 08, 2002 10:58 AM

=INDIRECT(A2&"!B2") [NT}

Posted by XLmon on February 08, 2002 10:59 AM

this might be what youre looking for...
put this code in a module:
Function valfromsheet() As Variant
Dim sheet As String
sheet = Sheets("Sheet1").Range("A2").Value
valfromsheet = Sheets(sheet).Range("B2").Value
End Function
then put this in Sheet 1 A1: =valfromsheet()
it worked for me, but I don't know how to make it update/recalculate automatically

Posted by XLmon on February 08, 2002 11:04 AM

Aladin gets the award

I see Aladin was faster and had a much better solution. Good Job!

Posted by Ferdinand on February 11, 2002 6:39 AM

Kudos to Aladin - it works!