+ Reply to Thread
Results 1 to 7 of 7

Multi-level data validation (perhaps using VLOOKUP?)

  1. #1
    Registered User
    Join Date
    12-15-2010
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    15

    Multi-level data validation (perhaps using VLOOKUP?)

    Hello all,

    I am trying to create a multi-level data validation structure so that the second validation list is populated based on what the user selects in the first list. So for example, if the first field was "Company", and the second field was "Products", then I'd like the "Products" to populate based on the user's selection of Company.

    The Company drop-down is straightforward as I just have a validation list "=CompanyList" with a defined named range.

    For data integrity purposes it is essential that the names in the CompanyList remain the way they are, including spaces and special characters. So I cannot create a different named range for each company name since named ranges cannot contain spaces.

    Basically I would like to be able to put something into the validation list formula such as "=VLOOKUP(Company,ProductTable,2)" but this would return only one value, whereas I need to return a list of values?

    Many thanks in advance

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Multi-level data validation (perhaps using VLOOKUP?)

    Use

    =INDIRECT(VLOOKUP(Company,ProductTable,2,FALSE)) in list field in Data Validation alongwith separate defined name ranges for each company
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    12-15-2010
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Multi-level data validation (perhaps using VLOOKUP?)

    Unfortunately I cannot use named ranges for the Company because the items in CompanyList contain spaces and special characters. These need to be maintained for data integrity purposes (the data is stored in a database and Company string must match exactly to populate the database).

  4. #4
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Multi-level data validation (perhaps using VLOOKUP?)

    Dont create named ranges for the compnay name itself. Instead use a defined name range assigned to each company by means of a lookup.

    See attached for details.

    Does this work for u?
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-15-2010
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Multi-level data validation (perhaps using VLOOKUP?)

    Ah, I see. Perfect, thanks!

  6. #6
    Registered User
    Join Date
    08-05-2008
    Location
    Glendale, CA
    Posts
    11

    Re: Multi-level data validation (perhaps using VLOOKUP?)

    Hi,
    So what happens if you have 1000 company names, let's call them customers. Would you be creating 1000 Name ranges?
    Furthermore, what if you need to fill in the address1, address2, City, State, ZP, and country for the customer you have selected?
    I just ask because I am trying to do the same thing: that is, select the customer (Bill-to info), with VLOOKUP, I am filling in the address information. Then From the selected "company" (I'll call it CUSTOMER) I need to select one of many SHIP-TO's and populate the address of that particular SHIP-TO record.
    How would you do that?

  7. #7
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Multi-level data validation (perhaps using VLOOKUP?)

    Hello francisco, and welcome to the forum.

    Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

+ 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