Home |
Search |
Today's Posts |
#1
|
|||
|
|||
vlookup with validation table
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. -- jparker ------------------------------------------------------------------------ jparker's Profile: http://www.excelforum.com/member.php...o&userid=26155 View this thread: http://www.excelforum.com/showthread...hreadid=394806 |
#2
|
|||
|
|||
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 P1:P10? 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" wrote in message ... 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. -- jparker ------------------------------------------------------------------------ jparker's Profile: http://www.excelforum.com/member.php...o&userid=26155 View this thread: http://www.excelforum.com/showthread...hreadid=394806 |
#3
|
|||
|
|||
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. -- jparker ------------------------------------------------------------------------ jparker's Profile: http://www.excelforum.com/member.php...o&userid=26155 View this thread: http://www.excelforum.com/showthread...hreadid=394806 |
#4
|
|||
|
|||
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 DataValidation 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 "jparker" wrote in message ... 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. -- jparker ------------------------------------------------------------------------ jparker's Profile: http://www.excelforum.com/member.php...o&userid=26155 View this thread: http://www.excelforum.com/showthread...hreadid=394806 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Vlookup with validation | Excel Worksheet Functions | |||
Table Array in VLOOKUP Relies on Data Validation | Excel Worksheet Functions | |||
I have a validation box on one page and a table on the other how . | Excel Discussion (Misc queries) | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Validation List and VLookup are ackting strange | Excel Worksheet Functions |