+ Reply to Thread
Results 1 to 4 of 4

Excel 2007 : multi level dependent list

  1. #1
    Registered User
    Join Date
    05-12-2010
    Location
    Wayne, PA
    MS-Off Ver
    Excel 2003
    Posts
    6

    multi level dependent list

    I need help with understanding how to add a third dependent list. The attached spreadsheet has the following tabs:

    form = this shows the 3 levels that I need. Data Validation has already been defined for Levels 1 and 2.

    values = values for each of the 3 levels

    Guidelines/instructions I've come across have been helpful until it gets to explaining the 3rd level. I get that each name list has to be unique. One set of instructions suggested combining the entries in the first two columns, which didn't make sense. I then tried an 'if' calculation but the end result wasn't the full list -- just the first line entry. So I'm close but have hit a wall in figuring this out.

    Any suggestions?
    Attached Files Attached Files
    Last edited by MWinder; 05-13-2010 at 07:57 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: multi level dependent list

    See attached.

    You need to make a named range for each possible selection in preivious data validation.

    To allow for items with spaces, you need to alter the INDIRECT formula used...

    so the data validation formula for E2 is:

    =INDIRECT(SUBSTITUTE($C$2," ",""))
    Attached Files Attached Files
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    05-12-2010
    Location
    Wayne, PA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: multi level dependent list

    This is where I'm lost and I guess thick headed. My 'job family' values are:
    HR
    Legal
    Sales
    Consulting
    I've used these as named values for Level 2. Since I've already used these values, how am I supposed to describe the columns for the 'job titles'? I understand the formula you provided, but Level 3 isn't being changed when different values in Column A are selected.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: multi level dependent list

    You have to name the ranges in other sheet where the individual lists reside.

    The named ranges should be the same as the elements you can choose in the 2nd validation and remove spaces between words.

+ 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