vlookup with validation table

[jparker]

First, I know about the Copy-Paste Special command. However, I want to
use a vlookup table that will lookup an item that is typed in cell A1
and display the result in cell A2. The lookup table is cells P1 to
P10. That part is simple. The cells in the lookup table are blank
except for their own validation table. I would like cell A2 to display
the validation table for the value of cell A1.

 

[Biff]

Hi!

The lookup table is cells P1 to P10. That part is simple. The cells in the
lookup table are blank except for their own >validation table

When you say: "validation table" are you talking about a Data Validation
drop down list?

If that's the case, what is the source for the drop downs you now have in
P110?

Those individual sources are what you need to make this work.

So, what you want is a user selected drop down source, right?

Actually, this is very easy but somewhat difficult to explain. If I'm on the
right track post back and let me know.

Biff

 

[jparker]

Yes, cells P1..P10 have their own unique data validation drop list. The
source of each list is defined in the NAME BOX. For example: cell P1
will have a data validation table named as ITEM1, P2 has a table named
as ITEM2, and so forth. The ITEM1 table is located in cells Z1..Z3.
The entries are Z1=Red, Z2=White, Z3=Blue. The ITEM2 table is in cells
Z4..Z6. The entries are Z4 = green, Z5 = yellow, Z6 = black. P3..P10
are similar.

When I type ITEM1 in cell A1, I would like to be able to use vlookup to
display the drop down list in A2 (once I move the cursor there). A2
should display the list and give me the choice of selecting either red,
white, or blue. Thanks.

 

[Biff]

Hi!

OK, here's how you do this.....

Make a list somewhere of all the named ranges....

Assume that list is in the range L1:L10

L1 = ITEM1
L2 = ITEM2
...
L10 = ITEM10

Now, select cell A2

Goto Data>Validation
Select: List
In the Source box enter this formula:

=CHOOSE(MATCH(A1,L1:L10),item1,item2,item3,item4,......item10)

Click OK.

If cell A1 is empty when you enter the above formula a message will pop up
saying that the source currently evaluates to an error and will ask if you
want to continue. Just click on YES.

So, if you enter in A1, ITEM7, then the ITEM7 table will be the drop down
list in cell A2.

You could even have a drop down in A1 that lets you pick which table you
want to use.

Biff