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.

	A	B	C	D
1	WSHED_Name	Dam_Name
2	Alamo Arroyo Watershed	Alamo Arroyo WS NRCS Site 1	Watershed:	Aquilla-Hackberry Creek Watershed
3	Aquilla-Hackberry Creek Watershed	Alamo Arroyo WS NRCS Site 3
4	Attoyac Bayou Watershed	Aquilla-Hackberry Creek NRCS Gss 14-1	Site:
5	Auds Creek Watershed	Aquilla-Hackberry Creek NRCS Gss 15-1
6	Bennett Creek Watershed	Aquilla-Hackberry Creek NRCS Gss 20-1
7	Big Creek (Brazos County) Watershed	Aquilla-Hackberry Creek NRCS Site 10
8	Big Creek (Tri-County) Watershed	Aquilla-Hackberry Creek NRCS Site 15
9	Big Sandy Creek Watershed	Aquilla-Hackberry Creek NRCS Site 17
10	Blanket Creek Watershed	Aquilla-Hackberry Creek NRCS Site 19A
11	Bosque Bottomlands	Aquilla-Hackberry Creek NRCS Site 2
12	Brady Creek Watershed	Aquilla-Hackberry Creek NRCS Site 20
13	Brown-Mullin Creek Watershed	Aquilla-Hackberry Creek NRCS Site 21A
14	Brownwood Laterals Watershed	Aquilla-Hackberry Creek NRCS Site 23A
15	Calaveras Creek Watershed	Aquilla-Hackberry Creek NRCS Site 3
16	Camp Rice Arroyo Watershed	Aquilla-Hackberry Creek NRCS Site 6
17	Caney Creek Watershed	Aquilla-Hackberry Creek NRCS Site 7
18	Castleman Creek Watershed	Aquilla-Hackberry Creek NRCS Site 8
19	Cedar Creek Watershed	Aquilla-Hackberry Creek NRCS Site 9
20	Chambers Creek Watershed	Attoyac Bayou WS NRCS Site 11
21	Chiltipin-San Fernando Watershed	Attoyac Bayou WS NRCS Site 12
22	Choctaw Creek Watershed	Attoyac Bayou WS NRCS Site 15
23	Clear Creek (Middle Colorado) Watershed	Attoyac Bayou WS NRCS Site 18A

<tbody>
</tbody>

_{^{<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!