Reference Excel worksheets dynamically

Question

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!

Answer 1

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)