Is it possible to use the contents of a cell to reference a different
sheet?

Sheet 1 (Named Master)
Sheet 2 (Named Test)
Sheet 3 (Named Development)
Sheet 4 (Named Production)

I'd like to be able to perform a VLOOKUP on Sheets 2,3,& 4 using the
contents of a cell from sheet 1 as the array.

For Example:
Sheet 1, cell A1 would have one of the names of sheets 2,3,or 4.

=VLOOKUP(A5,*A1!*$A:$F,5,TRUE)

I know the above example won't work. Can it be done?

Thanks!


--
JoeM
------------------------------------------------------------------------
JoeM's Profile: http://www.hightechtalks.com/m491
View this thread: http://www.hightechtalks.com/t2307541

Yes, you can do this by using the INDIRECT( ) function - you can build
up the sheet and the range within INDIRECT, something like;

=VLOOKUP(A5,INDIRECT(A1&"!$A:$F"),5,TRUE)

Pete

One way:

=VLOOKUP(A5,INDIRECT("'" & A1 & "'!A:F"), 5, TRUE)

In article ,
JoeM wrote:

Is it possible to use the contents of a cell to reference a different
sheet?

Sheet 1 (Named Master)
Sheet 2 (Named Test)
Sheet 3 (Named Development)
Sheet 4 (Named Production)

I'd like to be able to perform a VLOOKUP on Sheets 2,3,& 4 using the
contents of a cell from sheet 1 as the array.

For Example:
Sheet 1, cell A1 would have one of the names of sheets 2,3,or 4.

=VLOOKUP(A5,*A1!*$A:$F,5,TRUE)

I know the above example won't work. Can it be done?

Thanks!

Thank you very much! That did the trick...


--
JoeM
------------------------------------------------------------------------
JoeM's Profile: http://www.hightechtalks.com/m491
View this thread: http://www.hightechtalks.com/t2307541