Dynamic Dependent List Dropdown from Unsorted Range

[mrMozambique]

Hi all. Greetings from Cape Town.

I'm stuck with a problem I have on a tool I'm using to collect health data in Mozambique. Users must enter health results at clinics and hospitals in the various provinces and districts. I have a master list of sites (clinics/hospitals). I am able to create the necessary cascading comboboxes (dependent dropdown lists) when the users select Province and District (using named ranges for the districts' sites and the "indirect" validation method in the data entry form). However, my list isn't comprehensive and users need to add their own to my master list. I've created a form to allow users to add their sites, but then they don't appear in my site dropdown list in the data entry form because the provinces/districts are now unsorted. Is it possible to create a dependent list based on unsorted data like you would in Access with a combobox? For example, in the list below, I could want S99 and S100 to appear in my list when the user chooses P1 in the province dropdown and D1 in the District dropdown. In Access this is no problem because comboboxes are based on queries, but Excel is stumping me.

Provinces Districts Sites
P1 D1 S99
P2 D50 S1
P1 D2 S77
P3 D20 S66
P1 D1 S100

Thanks in advance for any help.

 

[Joneye]

Can you post any test data, I have a simliar workbook i use in work, thats reginolised with multiple users feeding back to one dashboard sheet.

 

[mrMozambique]

Thanks, Joneye.

I've uploaded some test data here: http://www.jpglobalinc.com/test_data.xls

You can see that the sites for Cabo_Delgado province are mixed. My districts dropdown depends on Cabo_Delgado being sorted and the range called "cabo_delgado" being assigned to the districts to the right of the sorted list of "cabo_delgado" provinces. The same is true for the sites vis-a-vis the districts.

I appreciate any help you can provide.

 

[grizz]

take a look here --- there maybe somthing that will work for you

https://sites.google.com/a/madrocketscientist.com/jerrybeaucaires-excelassistant/files

 

[mrMozambique]

Thanks, Grizz. I ended up using your DependentLists-SelfSorting sample. Due to the number of ranges I have ~75, it's a little slow to calculate. But, it does what I'm looking for. I combined it with a few macros and a user form to allow users to add options to the ranges.

If you ever discover how to improve the calculating speeds, I'd love to hear about it. I think it's the countif function causing the most trouble.

Cheers!