Dependent and dynamic drop down

[mg6116]

I have a sheet "Addresses" with 2 columns A and B (EventType and Location). For every event type there are multiple addresses (for instance 5 lines of the event type "Concert" each with a different venue address).
On another sheet I have an empty list with the same 2 columns. I would like to create a dynamic dependent dropdown on the second column that does 2 things:
1. Give me only the list of locations for the event I add in the first column (dependent). So, in case of "Concert", only the locations of the concert venues from sheet "Addresses" should be in the dropdown.
2. When the list on sheet "Adresses" grows, the dropdown should grow also with the newly added locations (if I add a new concert venue in sheet "Addresses" for instance).
I have been struggling with Index and Match but without success. If possible, I would like to avoid volatile functions as Offset and Indirect. Can anyone help?

 

[Belleke]

Start posting an excel example.

 

[mg6116]

In attachment an Excel example with very few lines. It is sheet "To Do" where I need the dropdown. It is an .xlsm file but I deleted all macro's.

 

[mg6116]

Start posting an excel example.

In attachment an Excel example with very few lines. It is sheet "To Do" where I need the dropdown. It is an .xlsm file but I deleted all macro's. Forget about the previous file. It was incomplete.

 

[p45cal]

Does the attached work for you? (It depends on your version of Excel.)

 

[mg6116]

Does the attached work for you? (It depends on your version of Excel.)

That works great. The only problem is that the sheet "Locations" can grow with extra lines and when it does, they do not show in the drop down. Is there a way to make it dynamic?

 

[mg6116]

Does the attached work for you? (It depends on your version of Excel.)

I think I found it. I changed the transpose table as follows:
1. to find the unique event types: =UNIQUE($A$2:INDEX($A:$A;COUNTA($A:$A)))
2. to find the unique locations: =TRANSPOSE(UNIQUE((FILTER($B$2:INDEX($B:$B;COUNTA($B:$B));$A$2:INDEX($A:$A;COUNTA($A:$A))=AE2;""))))

Since the number of even types is well known and limited , I can drag the second statement as far as needed.

Thanks for the help! I have been struggling for days. I should have asked for help sooner.

 

[mg6116]

Once you remove a big stone on the road, you can go further and further. I changed the look and feel an made the transpose table vertical for readability's sake. So now the statements are:
1. to find the unique event types: =SORT(TRANSPOSE(UNIQUE($A$2:INDEX($A:$A;COUNTA($A:$A))));1;1;TRUE)
2. to find the unique locations: =SORT(UNIQUE(FILTER($B$2:INDEX($B:$B;COUNTA($B:$B));$A$2:INDEX($A:$A;COUNTA($A:$A))=D1;""));1;1;FALSE)

This gives me a table with the event types as headers (sorted alphabetically form left to right in row 1 and all dependent locations sorted alphabetically starting from row 2. And all this dynamically. When lines are added to the table Locations, they will show up.

I had to adapt the names in Name Manager of course. Thanks again!

I post the corrected Excel file in case anyone else might need this kind of feature.

 

[pureassignment]

There had been projects that as I was to perform similar tasks. As an hire excel expert, I would perform as it is shown below;

When I deal with such problems, I usually;

1. Assign a macro to copy my data to somewhere
2. Apply a remove duplicate, and maybe sort
3. Count rows and keep the total row count in a variable
4. Define that interval as a range using the row count kept in mind in step3
5. And finally, set the data validation to the defined range.

That sounds complex, but with the help of AI coder and macro recorder, takes only minutes to complete excel formula