MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Data Validation


Posted by David on January 20, 2002 8:58 PM

Is there a way to make data validation lists dependent on previous selections? For example, if in cell A2 you have a selection list of 'Australia' and 'USA', how do you get the selection list in cell A3 to change to 'Sydney' and 'Melbourne' if 'Australia' is selected and 'Washington' and 'New York' if 'USA' is selected?


Posted by Aladin Akyurek on January 20, 2002 11:10 PM

David --

Make first the following 1-column lists in different ranges in some worksheet:

Australia
USA

Select the cells of this list and name it e.g., COUNTRIES via the Name Box on the Formula Bar.

The second list:

Sydney
Melbourne

Select the cells of this list which you must name it Australia via the Name Box on the Formula Bar.

The third list:

Washington
New York

which you must name it USA via the Name Box on the Formula Bar.

Now activate A2.
Activate Data|Validation.
Choose List for Allow, and enter as Source:

=COUNTRIES

Now activate A3.
Activate Data|Validation.
Choose List for Allow, and enter as Source:

=INDIRECT(A2).

Aladin

============