Excel 2007 : Dependent Dropdown (Data Validation)

mtpr220,

I do apologize. JB is correct in that you cannot use dynamic ranges as an indirect refernce in the data validation Lists... I overlooked that rule.

However, there is a way around it that does allow you to use "dynamic" ranges.

See attached. I only worked on the CONDUITCOPPER60C140F option....

The solution is based on the section in this link under title: Using Dynamic Lists

http://www.contextures.com/xldataval02.html

So, in your Lists Sheet, go back to redefining the name CONDUITCOPPER60C140F as simply the first cell in the column, ie. Lists!$D$1

Then add a new name same as above with suffix, Col. I.e. CONDUITCOPPER60C140FCol and define it as Lists!$D$D (ie. the whole column)

Now your Data Validation list formula is:

=INDIRECT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$2&$B$2&$C$2,"(",""),")",""),CHAR(176),"")," ",""))),1 0 COUNTA(INDIRECT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$2&$B$2&$C$2,"(",""),")",""),CHAR(176),"")," ",""))&"Col"))Ȧ1

Notice the reference part of the formula is the same as what I had given you before, and it is again used within the COUNTA() part of the formula, with the addition of the concatenation of the suffix "Col".