Dependant Drop Down List From Dynamic Table Data

Hi All,

I've been racking my brains trying to sort out an issue I have relating to drop down lists - my brains haven't been able to provide a solution, so I thought I would query yours.

I have a table that is quite large (around 1,000 rows) and is regularly updated and added to. One column in my table contains a unique site name, and another column contains the region that site is in (one of 7 regions). On another sheet I have two drop downs - one for the region and one for the site. This in turn will populate some other cells with data relating to that site. However, what I would like to be able to do, is once the region is selected, the second drop down list only shows the sites that are in the chosen region. This would ideally be sorted alphabetically as well.

I have previously created dependant drop down lists by creating a separate table on another tab with all the data for each region; however as the main table gets edited and added to, I'm not sure how I would get this to work. I have seen that array formulas are a possible solution to this as well, but due to the size of the table I don't believe this would be a viable solution.

If anyone could give me any pointers on how to do this, I would much appreciate it. Happy to go the VBA route if required. I have attached a simple spreadsheet with some dummy data that may give a better idea of what I need.

Excel Drop Down Query.xlsx

Many thanks in advance.

S

Both,

Many thanks for your help. Both work great - I've opted for the array formula as this will stay updated by using named ranges and can be alphabetised also - I was initially worried that by having a lot of array formulas it could slow things down, but doesn't seem to do. Really appreciate your assistance, keep up the good work!

Skins