+ Reply to Thread
Results 1 to 15 of 15

Populate Drop-down list based on selection from previous drop-down list

  1. #1
    Registered User
    Join Date
    12-11-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Populate Drop-down list based on selection from previous drop-down list

    OK, I found a file close to what I am looking for in This Thread

    but I am trying to add costs to the final selection - see attached 20121211 DependentLists reference costs.xlsx

    Not sure how to read that, maybe an IF reference, or VLookup?...

    I need blanks for the groupings, but the drop downs aren't ignoring them.

    I can't get the subtrade drop down selection to be dependent upon distance either.

    I have attached a file, I hope you can show me the light!
    Last edited by Diventus; 12-12-2012 at 08:36 AM. Reason: Removing mederators note. Thanks for moving this to its own thread.

  2. #2
    Registered User
    Join Date
    12-11-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Populate Drop-down list based on selection from previous drop-down list

    I did find some VB code that gets me as far, but I am not familiar enough to work with it.

  3. #3
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Populate Drop-down list based on selection from previous drop-down list

    "I need blanks for the subgrouping" doesn't mean anything to me. I looked at your sheet and the DV in column C is accurately depicting only the values needed from the category selected. It's working. Why break it?
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    12-11-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Populate Drop-down list based on selection from previous drop-down list

    Thank for the reply

    I just need to remove the blanks from the drop down list, I think they are showing up because I am using merged cells to eliminate duplicates.

    I need help putting the relative cost from the Raw Data tab to be placed into the Template!E2 cell.

    e.g. when I select 'Green Paint' from 'Paint shop' which is 'Local' I need the adjacent cell to automatically insert the cost of said green paint. $25

    I put some thoughts into cell E2, but not sure how to execute this...

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

    Re: Populate Drop-down list based on selection from previous drop-down list

    To avoid the blanks, you will need to relist the items in separate area of the sheet with no blanks between....

    If the products are all uniquely named, then you can use this formula to get your costs:

    =SUMIF('Raw Data'!$B$4:$F$7,C2,'Raw Data'!$C$4:$G$7)

    copied down.
    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.

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Populate Drop-down list based on selection from previous drop-down list

    This formula in D2 will get the "cost" of the item selected in C2 from the category selected in B2:

    =VLOOKUP(C2, OFFSET(Suppliers, 1, MATCH(B2, Suppliers, 0) - 1, 1000, 2), 2, 0)

  7. #7
    Registered User
    Join Date
    12-11-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Populate Drop-down list based on selection from previous drop-down list

    Thanks, that Vlookup formula works, and maintains the product listing.

    So I listed them with no gaps as you suggested, but that generates the duplicate issue I mentioned.

    See column Distance in the attached.
    Attached Files Attached Files

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Populate Drop-down list based on selection from previous drop-down list

    Cut those cells A13:E24 and paste in J1. Then your original DV formula in column C of your template goes back to working.

  9. #9
    Registered User
    Join Date
    12-11-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Populate Drop-down list based on selection from previous drop-down list

    20121214 DependentLists reference costs.xlsx

    I Cut and pasted those cells into J1, not sure how that helps though.

    The drop down either has blanks or duplicates (See column A in 'Template')

    Also the Subtrades field isn't dependent on distance, Can I use the Vlookup to do the same thing for ColB that is does for ColC?

    screenshot.jpg

    I have attached a screen shot if you didn't see it in the worksheet

  10. #10
    Registered User
    Join Date
    12-11-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Populate Drop-down list based on selection from previous drop-down list

    Bump: No response

  11. #11
    Registered User
    Join Date
    12-11-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Populate Drop-down list based on selection from previous drop-down list

    Bump: No response

  12. #12
    Registered User
    Join Date
    12-11-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Populate Drop-down list based on selection from previous drop-down list

    Bump: No response...

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

    Re: Populate Drop-down list based on selection from previous drop-down list

    Apologies for the delay.. I though JB had this....

    See attached.

    Note that a major difference between the initial thread you sited and your example is that the sited thread had no merged cells. Merged cells usually cause more harm than good.

    Because of these merged cells, we need to create separate lists at least for Distance and Subtrades....

    So in Raw Data sheet, column L I listed the Distances, and in M I listed the Subtrades.

    I created Named Ranges for each.

    Distance: ='Raw Data'!$L$1:$M$1 ( I assume once you have the distances set they will be fixed and not dynamic)

    For each Distance, assign the whole column to the name of that distance

    Far: ='Raw Data'!$M:$M

    Local: ='Raw Data'!$L:$L

    Then we name the whole table of Suppliers as:

    Suppliers: ='Raw Data'!$B:$G

    Now back in the Template sheet, the Data Validations:

    For column A, use List: =Distance

    For column B, use List: =OFFSET(INDIRECT($A2),1,,COUNTA(INDIRECT($A2))-1,1)

    For column C, use List: =OFFSET(INDEX(Suppliers,0,MATCH(B2,INDEX(Suppliers,3,0),0)),3,0,COUNTA(INDEX(Suppliers,0,MATCH(B2,INDEX(Suppliers,3,0),0))),1)

    Then in column D, for Costs use formula in D2:

    =IF(C2="","",SUMIF('Raw Data'!$B$4:$F$7,C2,'Raw Data'!$C$4:$G$7))

    copied down

    Hope it helps

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

    Re: Populate Drop-down list based on selection from previous drop-down list

    Bump - No acknowledgement.

  15. #15
    Registered User
    Join Date
    12-11-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Populate Drop-down list based on selection from previous drop-down list

    Thanks a lot NBVC, Thats great!

    I see the names in column L, M are the key to maintaining the labels in the drop down.

    Thanks a lot for your help, I'll be playing around with this and let you know how I get on!
    Last edited by Diventus; 01-11-2013 at 10:58 AM. Reason: update

+ 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