Dependent Validation lists

[TrainerJ]

I am stuck with this..

In Cell B8 I want to pick from the list Medical Experts which iv done.

Then When I select details form the list if there is a sub menu it shows up.

i.e the first sub menu is 3. Paediatrics: which should show list from E10:E25

Can you help please?

[ChemistB]

Can't give you specific instructions without you uploading your workbook. Take a look at this link. It describes exactly what I think you are looking for.

[NBVC]

I think ChemistB was trying to point you to:

http://contextures.on.ca/xlDataVal02.html

[TrainerJ]

Hi thanks for the link, but I still cant get it too work.

Attahced an exmaple in A22 I want a validation list from the list in B:2:B5...

If I select from list Paediatrics: I want a validation list to come up in B22 with all the data in C2:C17

Hope this makes sense. used the link and seems easy but I cant get it to work..

[NBVC]

See attached.

Note: Please be consistent in conventions.

I removed a colon from end of cell B5:

The range C3:C17 was named Paediatrics (See Insert|Name|Define)

Do the same for the other Medical Experts ... Don't include the title cell (e.g. C2)

To see data validation select cell and go to Data|Validation

[ChemistB]

Yep, that was the link. Sheesh. Is it Friday yet?

[TrainerJ]

I have the read the validation but How do I add more?

Say I want to add a sub col in D2 for the Ear, Nose and throat and I want to put the Validation in cell B23 to select the main drop down How to I make the formula work for the Ear col?

The Fornula shows

=INDIRECT(TRIM(SUBSTITUTE(MID($B$22,2,255),CHAR(160),"")))

I really want to understand how to do these and this works great but I dont understand it.

Sorry to sound stupid.

[NBVC]

In the same link, further down the page is a section called Using Items with Illegal Characters which would work nicely here.

See attached example.

Because your selections are not just simple one-word choices and they have multiple words and some illegal characters (for range naming), you will need to create special names for the ranges to put in the drop downs, and create a list of these names to associate to the originals. Then use Vlookup in the data validation formula to get the right info.

I inserted a column Between B and C... so the new column C has One-word names to match the descriptions in column B.

The list in column D is simply the same name.. could be abbreviated if you want (but no periods, etc). Name D3:D17: Paediatrics... you would create another list for Ear, Nose and Throat and simply name the list ENTGeneral or something similar... but make sure name is matched in column B.

Then your data validation formula for C22 would be: =INDIRECT(VLOOKUP(B22,$B$3:$C$5,2,0)) it looks up the item you selected in B22 and refers to the original table to find out what you named that item, then it indirectly refers to the table of the same name to provide you a new list.

You would do similar for all the Paediatric items, etc.. creating a list of applied names for each... see my list in column E.. each table would be named after that list... so G3:G5 is name ENT and H3:H6 is named Onc., etc..

Then data validation formula in D22 would be:

=INDIRECT(VLOOKUP(C22,$D$3:$E$17,2,0))


Note: When entering these data validation formulas you may get message that the result would be an error... don't worry about it, click Ok. It is because you didn't yet make selections in previous drop downs.

Hope this helps.

[TrainerJ]

That makes things clear. Cheers