Filter one list depending on contents of another plus intro to hurling

I recently met with a friend of a customer and after I let him into the secret of pivot tables, he asked me about helping him create two dropdown lists. The idea being that the contents of the second one would depend on what was chosen in the first one. In the example below, I’ve decided to use the provinces of Ireland as an example e.g. when Connacht is chosen from the list, only Galway, Mayo, Roscommon, Sligo and Leitrim appear in the list, whereas when Munster is chosen…another list appears.

I came across Debra Dalgleish’s approach here but I thought it would be more interesting to use tables to make the range dynamic…of course in my example, there will not be any counties added or moved from one province to another (except for hurling – my Irish readers will know what I am talking about. If you are not Irish, it’s the fastest game on grass.

Anyhow, back to the filtering lists.  In the video, I’ve chosen to use the 32 counties filtered by province.

[fvplayer src=”https://s3-eu-west-1.amazonaws.com/the-excel-expert/dependent_lists.mp4″]

1. Create a master list that has the four provinces: Connacht, Ulster, Munster, Leinster
2. Convert this list to a table. (Ctrl and T). Use the name box to call it Provinces.
3. Create 4 separate lists for each province listing the counties in each
4. Convert each county list to a table. Then use the name box to call it the appropriate province. Make sure they are EXACTLY the same. i.e. call Connacht Connacht.
5. In a separate sheet, highlight an area of the sheet.
6. Go to Data – Data Validation – Data Validation
7. From the dropdown list, choose List.
8. Click in the Source box, press F3 (to call up your list of range names). Click on Provinces
9. Check this dropdown list: it should give you Connacht, Ulster, Munster, Leinster
10. Highlight the column beside it. Go to Data – Data Validation – Data Validation
11. From the dropdown list, choose List.
12. Click in the Source box, type in =indirect(click on first cell of provinces dropdown)..press F4 until you have removed the dollar signs – otherwise it will only reflect the first cell in the list.
13. Now choose a province from the first list…check your second list…should only show the counties for that list…

You can download the county files here

This file (based on a farm assessment) already has the lists prepared.