Dependant Lists Question

[Guest]

This seems to be something not covered on the Webpage on dependant lists
http://www.contextures.com/xlDataVal02.html

I notice with the dependant lists it allows you to enter free form text,
unlike the master list which wont allow this. Is there a way of making the
dependant lists like any normal list in that you can't enter free form text?

 

[vitruvian]

This seems to be something not covered on the Webpage on dependant listshttp://www.contextures.com/xlDataVal02.html

I notice with the dependant lists it allows you to enter free form text,
unlike the master list which wont allow this. Is there a way of making the
dependant lists like any normal list in that you can't enter free form text?

Have you thought about using the list on one sheet (even hiding it if
necessary), and then using define names (Insert > Name > Define), and
then on the worksheet that you want the drop-down list (as this is
what I am thinking that you are after, you can then use Data
Validation for you list... Data > Validation...

Change the drop-down on the dialogue to list and then put in
=[DefinedNameHere]

And then you can extend the validation to the cells that you want...
The only draw back is that text can be entered but it has to match
what is already in the list you defined, and if it doesn't it will
prompt the user to either retry or cancel what they are doing

 

[Roger Govier]

Hi Alex

If you tick the Show error alert on the Error alert tab when setting up
the DV, it does behave in exactly the same manner for dependant lists.

 

[Guest]

Hi Roger,

Where abouts do you mean for show error tab? In Validation? Because it is
ticked in there, it wont let you enter free form text if there is something
in the master cell, but if the master cell is blank you can enter whatever
you like in the dependant cell.

 

[Guest]

I have sorted it, I just unticked Ignore Blanks in Validation.

 

[Roger Govier]

Hi Alex

but if the master cell is blank you can enter whatever
you like in the dependant cell.

You had not mentioned this before, and I hadn't thought to mention
Ignore Blanks.

I see you have found it though. Glad you are sorted.