# Return value based on 2 dependent drop down lists

1. ## 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.

2. ## 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. ## 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

4. ## 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. ## 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.

6. ## 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?

Arsene

7. ## 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. ## 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.)

9. ## 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

10. ## 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. ## 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/

12. ## 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

13. ## 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. ## 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. ## Re: Return value based on 2 dependent drop down lists

find the attached file

16. ## Re: Return value based on 2 dependent drop down lists

Indeed!

Thank you for sharing part of your genius mind!!

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. ## Re: Return value based on 2 dependent drop down lists

Favorited! I will take this apart later.

19. ## 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.

20. ## 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 -

Incase your issue is not solved, you can undo it as follows -

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.

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