Reference Excel worksheets dynamically

excel-2007 excel-formula
Thinkwell picture Thinkwell · Aug 20, 2011 · Viewed 23.4k times · Source

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

Eddy picture Eddy · Aug 20, 2011

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)

Related questions

Excel formula to reference 'CELL TO THE LEFT'

I'm trying to do conditional formatting so that the cell color will change if the value is different from the value in the cell left of it (each column is a month, in each row are the expenses on certain …

Read More
Excel how to find values in 1 column exist in the range of values in another

I have two columns- column A which extends upto 11027(values) and column I which extends to 42000(values).Both the columns contains some code details. Something like this A B q123 as124 as124 gh456 ff45 q123 dd1 dd2 xx2 xx3 xx4 …

Read More
PivotTable to show values, not sum of values

I'm wanting to display a pivot table and for it to show me the actual values, one on each row, rather than a sum of the values. E.g. Name Jan Feb Mar Apr Bob 12 10 4 3 5 James 2 6 8 1 15 etc. My starting point …

Read More