+ Reply to Thread
Results 1 to 4 of 4

vlookup with validation table

  1. #1
    Registered User
    Join Date
    08-10-2005
    Posts
    2

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

  2. #2
    Biff
    Guest

    Re: vlookup with validation table

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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. #3
    Registered User
    Join Date
    08-10-2005
    Posts
    2

    Reply

    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.

  4. #4
    Biff
    Guest

    Re: vlookup with validation table

    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

    "jparker" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1