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.

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.

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

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

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,

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!

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.

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/

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.

Indeed!

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

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

Favorited! I will take this apart later.