Dynamic Dropdown List

[angusfire]

I have two dropdown lists. The first one (D2) lists all the Watersheds in my area. The second one (D4) I need to list all the Sites located within that watershed. See table below. It is preferred that the Sites list only have the site number; i.e. "Site 1, Site 2, etc." I have several Index:Match combinations within Data Validation without any luck. Any help is appreciated.

[TABLE="width: 1203"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]WSHED_Name
[/TD]
[TD]Dam_Name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Alamo Arroyo Watershed[/TD]
[TD]Alamo Arroyo WS NRCS Site 1[/TD]
[TD="align: right"]Watershed:[/TD]
[TD="colspan: 4"]Aquilla-Hackberry Creek Watershed[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Aquilla-Hackberry Creek Watershed[/TD]
[TD]Alamo Arroyo WS NRCS Site 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Attoyac Bayou Watershed[/TD]
[TD]Aquilla-Hackberry Creek NRCS Gss 14-1[/TD]
[TD="align: right"]Site:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Auds Creek Watershed[/TD]
[TD]Aquilla-Hackberry Creek NRCS Gss 15-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Bennett Creek Watershed[/TD]
[TD]Aquilla-Hackberry Creek NRCS Gss 20-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Big Creek (Brazos County) Watershed[/TD]
[TD]Aquilla-Hackberry Creek NRCS Site 10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Big Creek (Tri-County) Watershed[/TD]
[TD]Aquilla-Hackberry Creek NRCS Site 15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Big Sandy Creek Watershed[/TD]
[TD]Aquilla-Hackberry Creek NRCS Site 17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Blanket Creek Watershed[/TD]
[TD]Aquilla-Hackberry Creek NRCS Site 19A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Bosque Bottomlands[/TD]
[TD]Aquilla-Hackberry Creek NRCS Site 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Brady Creek Watershed[/TD]
[TD]Aquilla-Hackberry Creek NRCS Site 20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Brown-Mullin Creek Watershed[/TD]
[TD]Aquilla-Hackberry Creek NRCS Site 21A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Brownwood Laterals Watershed[/TD]
[TD]Aquilla-Hackberry Creek NRCS Site 23A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Calaveras Creek Watershed[/TD]
[TD]Aquilla-Hackberry Creek NRCS Site 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Camp Rice Arroyo Watershed[/TD]
[TD]Aquilla-Hackberry Creek NRCS Site 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Caney Creek Watershed[/TD]
[TD]Aquilla-Hackberry Creek NRCS Site 7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Castleman Creek Watershed[/TD]
[TD]Aquilla-Hackberry Creek NRCS Site 8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Cedar Creek Watershed[/TD]
[TD]Aquilla-Hackberry Creek NRCS Site 9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Chambers Creek Watershed[/TD]
[TD]Attoyac Bayou WS NRCS Site 11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Chiltipin-San Fernando Watershed[/TD]
[TD]Attoyac Bayou WS NRCS Site 12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Choctaw Creek Watershed[/TD]
[TD]Attoyac Bayou WS NRCS Site 15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]Clear Creek (Middle Colorado) Watershed[/TD]
[TD]Attoyac Bayou WS NRCS Site 18A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
_{^{<strike>
</strike>}}

Thanks in advance!​

 

[Eric W]

Find an empty column to use as a helper column, for example column E. Put this formula in E1:

=IFERROR(TRIM(SUBSTITUTE(SUBSTITUTE(INDEX(B:B,SMALL(IF(ISNUMBER(SEARCH(TRIM(SUBSTITUTE($D$2,"Watershed","")),$B$2:$B$25)),ROW($A$2:$A$25)),ROWS($E$1:$E1))),SUBSTITUTE($D$2,"Watershed",""),""),"NRCS","")),"")

and confirm with Control+Shift+Enter. Drag down the column as needed. You can put this column on another sheet, or hide it. Now select D4, Click Data Validation, select List, and enter this as the source:

=OFFSET($E$1,0,0,SUMPRODUCT(--($E$1:$E$25<>"")))

 

[angusfire]

Thanks!! Works great!!!

Find an empty column to use as a helper column, for example column E. Put this formula in E1:

=IFERROR(TRIM(SUBSTITUTE(SUBSTITUTE(INDEX(B:B,SMALL(IF(ISNUMBER(SEARCH(TRIM(SUBSTITUTE($D$2,"Watershed","")),$B$2:$B$25)),ROW($A$2:$A$25)),ROWS($E$1:$E1))),SUBSTITUTE($D$2,"Watershed",""),""),"NRCS","")),"")

and confirm with Control+Shift+Enter. Drag down the column as needed. You can put this column on another sheet, or hide it. Now select D4, Click Data Validation, select List, and enter this as the source:

=OFFSET($E$1,0,0,SUMPRODUCT(--($E$1:$E$25<>"")))

 

[Eric W]

Happy to help!