PDA

View Full Version : Sheet Reference based on a Cell Value???



infinity
06-16-2014, 01:52 PM
Hello all,

I was wondering if there is a way to use a value in a cell (that cell value would exactly match the name of one of the worksheets) as a sheet reference in a formula?

ex. cell B4 = "Widget"
one of the sheets would also be named "Widget"
I want to use the cell value to reference the sheet name.

a formula would read...

=vlookup($A4,'Widget'!$A$2:$Z$10000,match(P$1,'Widget'!$A$1:$Z$1,0),0)

in this formula I would want it to use the cell value in B4 (Widget) in place of the sheet reference in the formula ('Widget'!)

Thank you all!

p45cal
06-16-2014, 02:38 PM
try:
=VLOOKUP($A4,INDIRECT($B4 & "!$A$2:$Z$10000"),MATCH(P$1,INDIRECT($B4 & "!$A$1:$Z$1"),0),0)
but it your sheet names have got spaces in it needs to be:
=VLOOKUP($A4,INDIRECT("'" & $B4 & "'!$A$2:$Z$10000"),MATCH(P$1,INDIRECT("'" & $B4 & "'!$A$1:$Z$1"),0),0)