Reference Excel worksheets dynamically

10
votes

I have a problem that should be so simple, but I'm not getting my head around it. I do printed pricelists for a store and this year they've split up the part numbers into 5 worksheets instead of one.

When a user wants to print out a pricetag, she enters the = into C10, clicks the Worksheet "Pricelist" and navigates to the part number she needs.

The resulting formula for C10 is: =Pricelist!B40

E10 must contain more info about the part number so E10 formula is: =VLOOKUP(C10,Pricelist!B:N,2,FALSE)

However, now with the new worksheets she might select Worksheet "New_Items" in which case the resulting formula for C10 is: =New_Items!B40

How can I write the formula for E10 so that it references the same worksheet that C10 does.

I need E10 =VLOOKUP(C10,Pricelist!B:N,2,FALSE)

to automatically become *=VLOOKUP(C10,New_Items!B:N,2,FALSE)*

That make sense? Is that doable to have Excel modify a worksheet reference based on another cell's reference?

TIA!

3
excel-2007excel-formula

3 Answers

13
votes

Yes you can do this using INDIRECT() It might take a bit of work since you need to work out the name of the sheet somehow. 

=VLOOKUP(C10, INDIRECT( Concatenate(cell-with-sheet-name, "!B:N"),TRUE ) ,2,FALSE)
1
votes

Can you use vba?

If so, try this simple udf

Function MyLookup(ref As Range, Offset as Long) As Variant
    MyLookup = Range(ref.Formula).Offset(0, Offset)
End Function

Cell E10 =MyLookup(C10, 1)

1
votes

You could create the VBA UDF

Function GetShtNm(rng As Range) As String
    Application.Volatile

    If InStr(1, rng.Formula, "!") = 0 Then
        GetShtNm = vbNullString
    Else
        GetShtNm = Mid$(rng.Formula, 2, InStr(1, rng.Formula, "!") - 2)
    End If
End Function

and use this is the formula with the indirect provided by Eddy.

This UDF will let you dynamically track sheet name changes within the VLOOKUP INDIRECT function.