+ Reply to Thread
Results 1 to 5 of 5

Question on dependent lists with repeating options

  1. #1
    Registered User
    Join Date
    06-21-2013
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Question on dependent lists with repeating options

    Hi all,

    I am trying to create a worksheet in excel that allows the user to choose a certain type of fork lift truck using dependent lists. The four lists are (in desending order): type, capacity, brand, & model. I have been working on creating the dependent lists for a while, but the complexity of what I'm trying to achieve is creating some issues. All the tutorials I am finding online assume your lists are mutally exclusive. Aka, no option is repeated. But in my case, many options are repeated.

    For example, the following are all options I would like a user to have:

    4 wheel sit down > 4,000 lbs > Raymond > M75

    3 wheel sit down > 3,000 lbs > Caterpillar > E300

    Reach Truck > 4,000 lbs > Raymond > M80

    I can't figure out how to do something like this since both "Raymond" and "4,000 lbs" appear more than once in the list (and my actual list includes dozens of repeating items). Can anyone help me out?

  2. #2
    Forum Contributor
    Join Date
    08-15-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    245

    Re: Question on dependent lists with repeating options

    So, if I choose 4 wheel sit down, then I might have some number of option, but if I choose reach truck, I'd have different options? So you could lay the entire choice diagram out sorta like a family tree? And what you're looking to do is only allow the user to input the next item based on the previous choice, so they don't select a model that's not actually in the category they selected, right?
    <--- If you like the answer, press *.

  3. #3
    Registered User
    Join Date
    06-21-2013
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Question on dependent lists with repeating options

    Correct.

    The problem I have is, creating dependent lists is done (as far as I can tell) by naming lists. But I can only have one list named "Raymond", one list named "3,000 lbs" etc, and I need to have many lists with the same name for this to work.

  4. #4
    Forum Contributor
    Join Date
    08-15-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    245

    Re: Question on dependent lists with repeating options

    How about putting data validation on the cell, and using a list that is populated based on the output of the previous cell? You could use a macro to clear out the cells further down the line if one of the earlier cell values is changed.

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Question on dependent lists with repeating options

    Quote Originally Posted by zumbalj View Post
    You could use a macro to clear out the cells further down the line if one of the earlier cell values is changed.
    That's a very good point (observation).

    I'm willing to bet that 90% of the folks that want to do this don't realize they could end up with invalid unrelated selections because they didn't think of resetting the cells when a new selection is made.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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