Making Drop Down List Values rely on the previous drop down list's answer

[50yoExcelDummy]

Hello, I have the following setup:

<table class="MsoNormalTable" style="width: 157.75pt; margin-left: 5.15pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="210"> <tbody><tr style="height: 11.25pt;"> <td style="width: 31.75pt; border: 1pt solid windowtext; padding: 0in 5.4pt; height: 11.25pt;" nowrap="nowrap" valign="bottom" width="42"> Dept
</td> <td style="width: 31.5pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 11.25pt;" nowrap="nowrap" valign="bottom" width="42"> Cat1
</td> <td style="width: 31.5pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 11.25pt;" nowrap="nowrap" valign="bottom" width="42"> Cat2
</td> <td style="width: 31.5pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 11.25pt;" nowrap="nowrap" valign="bottom" width="42"> Cat3
</td> <td style="width: 31.5pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 11.25pt;" nowrap="nowrap" valign="bottom" width="42"> Cat4
</td> </tr> <tr style="height: 11.25pt;"> <td style="width: 31.75pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 11.25pt;" nowrap="nowrap" valign="bottom" width="42"> (a)
</td> <td style="width: 31.5pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 11.25pt;" nowrap="nowrap" valign="bottom" width="42"> (b)
</td> <td style="width: 31.5pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 11.25pt;" nowrap="nowrap" valign="bottom" width="42"> (c)
</td> <td style="width: 31.5pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 11.25pt;" nowrap="nowrap" valign="bottom" width="42"> (d)
</td> <td style="width: 31.5pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 11.25pt;" nowrap="nowrap" valign="bottom" width="42"> (e)
</td> </tr> </tbody></table>
In the Dept (a) cell, Im using this string in my Data Validation area to provide a Dynamic Drop Down List in my "Dept" column: =OFFSET(Dept,0,0,COUNTA(A:A)-1)
That I got to work .. or at least I think I do! <smile>

The problem lies when each Category (actually more of a sub category) b, c, d, & e above each rely on the previous to complete its fill down list.

I have a separate ss called NameLists that have about 20+Lists isolating everything into its nitche. (this is for a Vitamins, supplements, etc.. )
So for Example the Dept might be "Vitamins & Minerals" under that a menu with 3 items should come up that says "Vitamins; Mulit-Vitamins; Minerals.

If we choose Vitamins, there are 24 choices of which one of them would be: "Vitamin C & Bioflavonoids".

Then under That would be another list of 8 choices.

I managed to get the Dept to work,
I manged to get the second list which would be Cat1 above (b).
However, when I use the string in the Data Validation area like I did for (b), in Cat 2 (c) above .. I get Nuttin .. NOTTA .. in my cell...

any suggestions?
used in Cat1 (b) =INDIRECT(SUBSTITUTE(U2," ","_"))
used in Cat2 (c) =INDIRECT(SUBSTITUTE(V2," ","_"))

Thank You! </smile>

 

[rs2k]

Would this link be of any use to you?

http://contextures.com/xlDataVal02.html

HTH
Colin

 

[50yoExcelDummy]

Thanks for the reply! -
Actually, thats where I got the information that got me this far! <smile>

Things work for the Dept and the Cat1 level, but when I use the same string and modify it to look in the previous cell, V2 in this case, it just gives me a blank drop down menu.

I must be missing something... I just dont know what.

Thank You though, for your reply!</smile>