Cell Validation

[Ted]

Hello

Can anyone help me with this ...

On a worksheet the user can enter one of two values in cell A1. These are
defined by a named range. For each of the three potential values a named
range exists called the name of the value in A1. Cell B1 uses these names to
populate a pop up list according the the value in A1 using the formula
=indirect(A1) as the validation range for cell B1.

As a simple example:

A1 only allows 'Dog' or 'Cat'

If A1 is 'Dog' then the pop up list for B1 shows 'Alsation, Beagle, Kelpie'

If A1 is 'Cat' then the pop up list for B1 shows 'Burmese, Siamese, Tabby'

All this works well.

The problem is that if the user enters values in A1 and B1 which follow
correct validation and then changes A1 to another value B1 remains as
previously entered - ie a value which is not valid for the value in A1, even
though the pop up list is now populated with the correct values for the new
value in A1.

To continue the example:
If the user enters Dog and selects Beagle then changes A1 from Dog to Cat,
Beagle is still showing in B1.

How can I make B1 go blank if A1 is changed?

A further complication is that there is a 'New Row' button which runs a
procedure which enters a new row correctly formatted so the solution for row
1 needs to be automatically applied to row 2.

All help greatly appeciated.

Thanks

Ted

 

[Debra Dalgleish]

There's a sample file here that uses cross dependent data validation:

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

Under Data Validation, look for "Dependent Lists Country City"