Reference sheet in workbook to use dependent drop down lists

Hello all,

I have created a sheet that uses 2 dependent drop down lists (pipe size then pipe description). According to the options chosen from each drop down list (size then description), the price is displayed accordingly.
I am now trying to move these drop down lists to a different sheet within the same workbook. I only want to have the sheet where the drop down lists are currently located used as a reference sheet (where the raw data for the drop down lists is). If I simply copy and paste, the drop down lists become faulty and show different options/data.
I want to be able to at least copy one row of drop down lists from the "Piping and Fitting" sheet onto the "Summary". Once copied/referenced onto the summary sheet, I want to be able to drag the drop down the lists for the amount of rows I want to use.
I know this has to do with correctly referencing from the Name Manager.

On the attached file, the Piping and Fitting sheet works perfectly. On the other hand, the drop down lists from the Summary sheet are faulty.

Does anyone know how to correctly reference these?
Also, is this possible to do using a different workbook rather than a different sheet?

Thanks!

Hi ChemistB,

Thanks for the reply!

It did fix the issue I had with the drop down lists. However, I still see a problem with it.
As I change the pipe size from let's say 10'' to 10'' x, the description list doesn't change accordingly.
If I select 10" x, the 2nd drop down list should only have all the reducers as a selection, that's not the case here. It only shows all the pipe, tubing, elbows, tee options...the reducers do not even show up.
However, you'll see that the drop down lists from the Piping and Fitting sheet doesn't have this problem.

Also, even if I select the ones I want from the Summary sheet, the price does not update unlike the Piping and Fitting sheet

Thanks ChemistB!

That is great, seems to have fixed the problem!
I still can't get the price to update as a change the drop down lists on the Summary sheet...

Any ideas?


Thanks again!

Arsene

Thank you ChemistB, that works perfectly!

And I promise this will be my last post on this thread.
I am now trying to display the unit for the description I choose from the drop down list.
For example, if I choose Pipe or Tubing from the 2nd drop down, I want the unit to show "ft".
On the other hand, if I choose anything else other than Pipe or Tubing, like reducer, tee let's say, then I want the unit to show "ea".

I am able to return the correct unit when I choose Pipe, but if I choose Tubing, the wrong unit is displayed, I can't figure out why.
This is the formula I am using in cell D25:
=IF(ISERROR(SEARCH({"Pipe","Tubing"},B25,1)),"ea","ft")

I don't want this to be case sensitive.

Thanks again!

Finally found the correct formula that worked for me.
here it is (in cell D25)
=IF(ISNUMBER(SEARCH("Pipe",B25)),"ft",IF(ISNUMBER(SEARCH("Tubing",B25)),"ft","ea"))

If Pipe of Tubing is selected, then "ft" is displayed as a unit.
If anything else is selected (reducer, tee, elbow), then "ea" is displayed as a unit.

Thanks ChemistB for the help in the previous threads!