Sheet Reference based on a Cell Value???

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...

Code:

---

=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!

try:

Code:

---

=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:

Code:

---

=VLOOKUP($A4,INDIRECT("'" & $B4 & "'!$A$2:$Z$10000"),MATCH(P$1,INDIRECT("'" & $B4 & "'!$A$1:$Z$1"),0),0)

---