Thread: Need dynamic table_array formula that looks in different sheets

Let's say I have one tab with just my formulas and then 3 tabs of data which
we'll call RED, BLUE, & GREEN. In cell A1 on my formula page I created a
dropdown box using the List (Data\Validation).

How do I make a formula, vlookup in this case, in cell B5 to that looks as
A5 and refers to the sheetname in my dropdown box in cell A1 rather than the
sheetname itself? In other words I want to be able to choose RED, BLUE, or
GREEN in my dropdown box, and have my vlookup look in that selected tab.
Currently I have the formula as
=VLOOKUP(A5,RED!$A$1:$D$10,4,FALSE). I want the RED portion to correspond
to my dropdown box. In actuality I only want part of my table array to
change accordingly to my dropdown box. -Doug

=VLOOKUP(A5,INDIRECT("'"&A1&"'!$A$1:$D$10"),4,0)


Regards,

Peo Sjoblom

"Doug Laidlaw" wrote:

> Let's say I have one tab with just my formulas and then 3 tabs of data which
> we'll call RED, BLUE, & GREEN. In cell A1 on my formula page I created a
> dropdown box using the List (Data\Validation).
>
> How do I make a formula, vlookup in this case, in cell B5 to that looks as
> A5 and refers to the sheetname in my dropdown box in cell A1 rather than the
> sheetname itself? In other words I want to be able to choose RED, BLUE, or
> GREEN in my dropdown box, and have my vlookup look in that selected tab.
> Currently I have the formula as
> =VLOOKUP(A5,RED!$A$1:$D$10,4,FALSE). I want the RED portion to correspond
> to my dropdown box. In actuality I only want part of my table array to
> change accordingly to my dropdown box. -Doug
>

Thank you very much Peo!!! I had been trying to figure out the INDIRECT
function based on other postings, and your example was perfect for me to
apply into my spreadsheet. Also, many thanks to Frank Kabel who has also
posted help to INDIRECT questions.

"Peo Sjoblom" wrote:

> =VLOOKUP(A5,INDIRECT("'"&A1&"'!$A$1:$D$10"),4,0)
>
>
> Regards,
>
> Peo Sjoblom
>
> "Doug Laidlaw" wrote:
>
> > Let's say I have one tab with just my formulas and then 3 tabs of data which
> > we'll call RED, BLUE, & GREEN. In cell A1 on my formula page I created a
> > dropdown box using the List (Data\Validation).
> >
> > How do I make a formula, vlookup in this case, in cell B5 to that looks as
> > A5 and refers to the sheetname in my dropdown box in cell A1 rather than the
> > sheetname itself? In other words I want to be able to choose RED, BLUE, or
> > GREEN in my dropdown box, and have my vlookup look in that selected tab.
> > Currently I have the formula as
> > =VLOOKUP(A5,RED!$A$1:$D$10,4,FALSE). I want the RED portion to correspond
> > to my dropdown box. In actuality I only want part of my table array to
> > change accordingly to my dropdown box. -Doug
> >