angusfire

New Member
Joined
Feb 24, 2012
Messages
34
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.

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

<tbody>
</tbody>
<strike>
</strike>


Thanks in advance!
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,637
Find an empty column to use as a helper column, for example column E. Put this formula in E1:

Code:
=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:

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

angusfire

New Member
Joined
Feb 24, 2012
Messages
34
Thanks!! Works great!!! :)

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

Code:
=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:

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

Watch MrExcel Video

Forum statistics

Threads
1,123,088
Messages
5,599,664
Members
414,325
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top