+ Reply to Thread
Results 1 to 20 of 20

Return value based on 2 dependent drop down lists

  1. #1
    Registered User
    Join Date
    10-10-2013
    Location
    Dallas
    MS-Off Ver
    Excel 2007
    Posts
    17

    Exclamation Return value based on 2 dependent drop down lists

    Hello there,

    I have been working on this spreadsheet for a while now and I am kind of stuck.
    To provide some background info, I have a list representing pipe sizes with its description and cost, respectively.
    What I have accomplished so far 2 dependent drop down lists. For example, the first drop down lists shows all the available pipe sizes. Once a pipe size has been chosen, the second drop down list shows the different options available for that specific pipe size.
    What I am now trying to do is return the cost reflecting this pipe that I chose through the drop down lists.

    Although I was able to do this, it only seems to work for one selection.
    For example, I want to be able to add multiple drop down lists to show additional costs (1st row represents 1/2'', Copper K, cost and 2nd row represents 1/4'', Copper K, cost, etc.). I want to be able to add as many as I need (this would be used to estimate the cost of a new project).

    I found a way to do this but it is very uneffecient. It requires me to add an INDEX function for every drop box I want to add (very uneffecient if one project requires 150 different items to be lsited while another one could only require 20...).
    I am not sure if VLOOKUP could be used for drop down lists...

    I was hoping someone out there could help me out, this would be extremely helpful!

    Please note on the attachement, Reference sheet has all the backbone and formulas which is used for the Piping abd Fitting sheet.

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Return value based on 2 dependent drop down lists

    Hi

    This will need a simple macro.

    The macro will find every occurrence of your text in your first drop list

    It will then loop through each of those to find the one that matches your second drop box.

  3. #3
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Return value based on 2 dependent drop down lists

    Hello arsene2conde welcome to the forum, can you please update on what sheet you are seeking output and what are the criteria for the same as I am not getting your point clearly
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Return value based on 2 dependent drop down lists

    I did it with SUMPRODUCT:

    G3:
    =SUMPRODUCT(($A$3:$A$278=E3)*($B$3:$B$278=F3)*($C$3:$C$278))

    And copied downwards.

    cost estimation.xlsx

  5. #5
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Return value based on 2 dependent drop down lists

    Try this sample.

    Changing E3 or F3 will change G3

    To view the code. right click on the sheet name at the bottom of excel and select view code

    Enjoy.
    Attached Files Attached Files
    Last edited by mehmetcik; 10-10-2013 at 05:02 PM.

  6. #6
    Registered User
    Join Date
    10-10-2013
    Location
    Dallas
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Return value based on 2 dependent drop down lists

    Hi daffodil11

    Thank you! everything seems to be working except one thing...
    If I do use this function, it messed up some characteristics.
    For example, if I choose 1/2'' and Pipe, SS, Schedule 40, the price shows up as it's supposed to.
    However, if I drag and drop the E3 - G4 a couple rows down, the following drop downs are missing a few things.
    If you then choose 1''x...the second drop down shows all the options except the corrects ones. any sizes with an x after corresponds to a reducer.
    If I pick 1''x on my second drop down list, no reducer options show up...

    After typing this, I realized it has nothing to do with the SUMPRODUCT function but it is rather a problem with my spreadsheet.

    Anyway, do you know why this happens?

    Thanks again for your help!

    Arsene

  7. #7
    Registered User
    Join Date
    10-10-2013
    Location
    Dallas
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Return value based on 2 dependent drop down lists

    Hi hemesh,

    I am seeking output on sheet1 (Piping and Fitting).
    I am now realizing that there is a problem somewhere with my drop downs lists...
    If I choose from the firs line of drop downs 1/2'' and Tubin, SS, 304L, Bright Annealed, the cost displays correctly.
    However, if I go on to the second line of drop down lists and choose 1/2'' x as my size, only reducer options should show up.
    In my case, no reducer options show up but rather all the other options do.

    I am not sure why, any idea?

    I am using daffodil11's spreadsheet since SUMPRODUCT seems to be working the cost calculation.

    Thanks,

  8. #8
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Return value based on 2 dependent drop down lists

    I understand the issue now. All the contingent dropdowns beyond the first row are still showing the same dependancy of the first.

    You're trying to find a way to ease the creation of multiple dependant drop-downs. As far as I know, there is not a way to shorten the upfront work involved.

    You may need to recreate Reference!B2:B20 for each pair of contingent dropdowns.


    Here's a screenshot of our employee review form's Code Tab at max Zoom Out.

    dropdown explosion.png

    Our monthly tabs contains 4 pairs of drop downs. The first of each pair selects a category, and on the Code Tab that drives an autopopulate of one of the boxes on the left from one of the boxes of blue text at the top. The second dropdown pulls from that list, which is similar to what you're trying to do.

    It was a lot of work, and I didn't even bother eliminating the blanks at the bottom of the dropdowns like you did. (I'm still dissecting how you did that.)
    Last edited by daffodil11; 10-10-2013 at 05:58 PM.

  9. #9
    Registered User
    Join Date
    10-10-2013
    Location
    Dallas
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Return value based on 2 dependent drop down lists

    I see what you mean...
    That's what I was afraid of.

    I guess I need to find another way to do this. Using drop downs rather than typing each items everytime seemed the simplest way to do it. I didn't think dragging these drop downs would all depend on the initial drop down list.
    It seems a little odd though because I do not have this problem with the first drop down list for sizes...no matter what size I choose, the second line of drop down still shows all the size options. For example, if I chosse 1/2'' on my first drop down, I still have all the size choices on the second size drop down list...
    I only have this issue with the description drop down list. It maybe simply because the sizes drop down list is not dependent from anything (unlike the description drop down list).
    Sorry if it's a little confusing to understand my point

    I appreciate your help!

  10. #10
    Registered User
    Join Date
    10-10-2013
    Location
    Dallas
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Return value based on 2 dependent drop down lists

    Perhaps I will only use the first drop down as a search engine rather than trying to populate the sheet with all drop downs
    I will just have to copy and paste accordingly.

  11. #11
    Registered User
    Join Date
    10-10-2013
    Location
    Dallas
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Return value based on 2 dependent drop down lists

    I see.

    You can remove the blanks at the bottom of the dropdown by using the OFFSET and COUNTIF function. You should look at the "Name Manager" under formulas, that might help you understand better. (you can ignore the "cost", this can be used if you need a 3rd dependent drop down list)
    I updated my spreadsheet (attached), I found a typo in one of the name in "Name Manager".

    I recommend reading this, that's how you can remove the blanks from a drop down.
    http://www.get-digital-help.com/2010...lues-in-excel/
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Return value based on 2 dependent drop down lists

    Have a look at this ! I don't think you need to have a separate sheet for unique values
    I have changed the names you have given in your sheet. Cost column has Array formula if you change anything in column I3 downwards you just do not press enter instead hold control shift then hit enter which is an array confirmation.(you will see curly braces {} surrounding your formula)



    Regards
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    10-10-2013
    Location
    Dallas
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Return value based on 2 dependent drop down lists

    Wow hemesh!
    This is exactly what I was trying to do.
    I am still trying to fully understand the formulas you have used, some are really lengthy

    The only thing I noticed the drop down list do not show 12'', they all stop at 10''. I am still trying to see what is causing this, do you know why?

    Other than that, you nailed it!

    Thank you hemesh!
    daffodill11, check what hemesh posted, it solves the drop down independency issue we mentioned yesterday.

  14. #14
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Return value based on 2 dependent drop down lists

    Hello arsene I just fogot to add 2 go to name manager
    select Dropdown size and delete previous formula and copy paste below formula
    =INDIRECT("E3:E"&SUMPRODUCT(--ISTEXT('Piping and Fitting'!$E$3:$E$278))+2)

    that will do

  15. #15
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Return value based on 2 dependent drop down lists

    find the attached file
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    10-10-2013
    Location
    Dallas
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Return value based on 2 dependent drop down lists

    Indeed!

    Thank you for sharing part of your genius mind!!
    This spreadsheet will be extremely helpful to me.

  17. #17
    Registered User
    Join Date
    10-10-2013
    Location
    Dallas
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Return value based on 2 dependent drop down lists

    Thanks for the attachement!

    How would I go about updating the spreadheet if I decide to add on to my list (ex. add sizes, 13'', 14'', etc...)
    I tried replacing in all the formulas from row 278 to 1000 (to be safe). I also replaced all the ones that had row 278 to 1000 in the Name Manager.

    I also dragged the Helper Column and Unique Value down to update them accordingly.
    I added a 14'' size on row 279 with its descriptio to see if it would work.
    Now, the drop down list for sizes work as it's supposed to be (displays 14''), but then, the drop down list for description is empty...

    I don't know why

  18. #18
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Return value based on 2 dependent drop down lists

    Favorited! I will take this apart later.

  19. #19
    Registered User
    Join Date
    10-10-2013
    Location
    Dallas
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Return value based on 2 dependent drop down lists

    Never mind,

    I forgot to update the actual drop down list formula from 278 to 1000.
    Problem solved.
    Last edited by arsene2conde; 10-11-2013 at 11:01 AM.

  20. #20
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,163

    Re: Return value based on 2 dependent drop down lists

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Dependent drop down lists without creating unique named lists
    By pajordan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-11-2013, 12:20 PM
  2. [SOLVED] Dependent drop down lists based on dynamic data range
    By strud in forum Excel General
    Replies: 15
    Last Post: 04-19-2013, 08:08 AM
  3. [SOLVED] Column B drop down list with 2 options, columns C & E with dependent drop down lists
    By betic in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-26-2012, 03:47 PM
  4. IF THEN ELSE based on Three Dependent Drop Down Lists (VBA)
    By vtsoldier2010 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-15-2012, 09:56 AM
  5. Dependent Drop Down lists (multiple drop down)
    By jijy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-17-2007, 09:56 AM

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