Arrggh!! Dependent drop down lists...

**JSLampard** · 06-22-2012, 05:49 AM

Hi guys - I'm trying to produce a spreadsheet with drop downs, but each of these drop downs will have an effect on what the next drop down along will contain in it's drop down.

So, if user selects Hospital A, this will then prompt the next drop down along (wards) to only show in it's list those wards associated with Hospital A i.e. Ward AB, AC, AD, AE. If user selects Hospital B, the ward dropdown next to it will only populate it's list with Ward BA, BB, BC, BD etc....

Can anyone help??

**npamcpp** · 06-22-2012, 05:52 AM

Dependent data validation is explained in detail on Contextures: http://www.contextures.com/xlDataVal02.html

There are different approaches, depending on your data structure. The best advice can only be given if you share your data structure by uploading a file.

**Fotis1991** · 06-22-2012, 05:54 AM

Hi

Take a look here.. Depanted validation Lists..

http://www.contextures.com/xlDataVal02.html

**Fotis1991** · 06-22-2012, 05:55 AM

@ npamcpp

You were faster!

Did you see my example, yesterday?

**JSLampard** · 06-22-2012, 06:25 AM

Thanks guys - perhaps if I give you the variables and the reulting lists I need? As follows:

Hospital List:
Fordingbridge
Hythe
Romsey
Gosport
Havant
Alton
Chase
Fleet
Odiham
Petersfield
Lymington
Abbey House
Brendon Care

I want the first item which appears on this first drop down list to say - 'Select Hospital'

Once a hospital is selected I then want the ward drop down to automatically populate with the relevant wards associated with the hospital selected, as follows:

Fordingbridge = Ford Ward
Hythe = Hythe Ward
Romsey = Romsey Ward
Gosport = Sultan Ward
Havant = Havant Ward
Alton = Anstey Ward, Inwood Ward
Chase = Macilwain Ward
Fleet = Calthorpe Ward
Odiham = Odiham Ward
Petersfield = Rowan Ward
Lymington = Deerleap Ward, Longbeech Ward, Wilverley 2 Ward, MAU Ward, Knightwood Ward
Abbey House = Single Rooms Ward
Brendon Care = Dame Sheila Quinn Ward

So, once a user selects, say, Lymington Hospital, the ward drop down immeidately changes to show all the Lymington Wards in the list i.e. Deerleap Ward, Longbeech Ward, Wilverley 2 Ward, MAU Ward, Knightwood Ward. If user selects Alton Hospital, the ward drop down immediately shows Anstey Ward and Indood Ward as the two wards associated with this hospital...and so and and so forth....

If anyone can help, would be much appreciated.

**npamcpp** · 06-23-2012, 07:45 AM

The setup required for this is described in detail in the link that has been posted twice by two different members. Have you tried it? If you get stuck, post a file and show how far you got and where you got stuck.

**Marcol** · 06-23-2012, 09:20 AM

Dependent Lists can be tricky, especially when trying to use Dynamic Named Ranges.

In addition to the already mentioned sources of information try this workbook.

Add to the list in Columns C:D, the rest will update automatically.

The hospitals in Column C must be grouped, it isn't essential that the groups are sorted alphabetically, but that would be the easiest way to go.