This is a fast approach to add a level of sophistication and reduce input error in your spreadsheet. It is a great technique because it will flex, based on the value chosen in a precedent drop-down list.

In the above screenshot, select “Canada” for cell H63. The dependent drop-down list will be generated in the adjacent cell I63. As soon as it is finished, it will only permit spreadsheet users the option of Canadian Provinces or US States.

For this to work, it is necessary to define the data ranges for both the Provinces and the States; with the exact same name as defined in the drop-down list in the earlier screenshot.

Select “Formulas”, choose “Name Manager” and click on “New…” and complete the following detail – “Name:” Canada, “Scope:” Workbook and “Refers to:” [place relevant range of the Canadian Provinces]..

Repeat the above instructions for the array of US States.

To complete this exercise, select “Data” in the menu at the top of the spreadsheet and select “Data Validation”.

Under the “Settings” tab, select the drop-down list under “Allow” and select “List”. In the “Source” window, type this formula: “=INDIRECT(H63)”. As illustrated earlier, it is necessary to define the array of both Canadian Provinces and US States (by name) identically to their names in the “Country” column – otherwise the dependent drop-down list will return an error.

[youtube]http://www.youtube.com/watch?v=G1VdgMlPOWA[/youtube]