Use Cell Containing Worksheet Name in Formula

[jcurran13us]

I'm sure this can't be that hard, but I am trying to use a cell that
contains a worksheet name in a formula, but can't get it to work.

CellA1.value = "Sheet1"

Formula is VLOOKUP($A$1,Sheet1!A1:B4,2,FALSE)

but I don't want to use Sheet1, I want a reference to CellA1 that
contains the sheet name.

Any help would be appreciated!

 

[diablo]

I'm sure this can't be that hard, but I am trying to use a cell that
contains a worksheet name in a formula, but can't get it to work.

CellA1.value = "Sheet1"

Formula is VLOOKUP($A$1,Sheet1!A1:B4,2,FALSE)

but I don't want to use Sheet1, I want a reference to CellA1 that
contains the sheet name.

Any help would be appreciated!

Someone on this news group gave this to me once, so I won't take any credit
for it. It works great.
Create a macro with this code and run the macro.

Public Sub RenameSheet()
NewName = Range("A1").Value
ActiveSheet.Name = NewName
End Sub

Brian

 

[NIMISH]

All you have to do is to use INDIRECT function.

INDIRECT function converts any result that is evaluated to string to
the worksheet range or cell name.

Suppose cell D1 contains "A1" (as typed no quots) and A1 cell contains
say 50. Then INDIRECT(D1) would return 50.

Use VLOOKUP($A$1,INDIRECT(A1&"A1:B4"),2,FALSE)
In above formula A1 cell contains "Sheet1".

I am sure this formula is not on Sheet1 otherwise it would lead to
circular reference.

Nimish