Data Validation: Part 3 of 3

Make your spreadsheets stand out with Data Validation and Dependent Drop-Down Lists

In part one of this Data Validation series, I showed you how simple it is to add validation to your spreadsheets, increasing the ease and accuracy of data entry. In that post, I showed two ways of limiting the allowed values for any particular cell. The most straightforward method involves entering the values directly into the source box. Alternatively, you can enter the allowed values in a column somewhere else on the spreadsheet and then refer to that range of cells in the validation window. The downside to this method is that you must list the values on the same spreadsheet as the field on which you are applying validation, adding unnecessary clutter. Of course, as I mentioned, you could hide the column or put the values well off of the visible screen (e.g. column ZZ or something like that), but you’d have to unhide the column or scroll over to the far right of the spreadsheet every time you wanted to update the list. That is obviously not ideal.

A better way to accomplish this was discussed in part two of this Data Validation Series, where I showed how using a named range allows for a cleaner worksheet. I showed how listing the values you want to allow for a specific cell (field) on another sheet, then naming this range so that you can refer to it in the Data Validation window is cleaner and more efficient than listing them in the Data Validation window itself or separately in a list, but on the same sheet.

Because using the named range makes adding an item to the list difficult (You either have to change the referenced range area each time you add an item or you have to make the original range large enough to include further inclusions, which leaves unwanted spaces in your data validation list.), in Part 2a, I showed you a great workaround that utilizes Excels’ Table feature.

Now, in the final part of this series, I show you how to create dependent drop-down lists through validation. This can best be explained with an example.

Let’s say you have a sheet that asks for the City and State (See attached: Data_Tables3_dependentLists (92-03 version)  or Data_Tables3_dependentLists (07 version). For several reasons that might include the elimination of typos, you restrict the “State” list to California, Idaho, Washington, Arizona, and Nevada. You’d like to apply the same type of restrictions to the city. But to further restrict the options available to the end-user, you want the city list to be of cities located in the state that is selected in the “State” field. In other words, you want the “City” list to be dependent on the “State” value. Once Arizona is selected, for example, the list of options for city might include Phoenix and Tuscon. It won’t, however, include Seattle (WA) or Boise (ID).

Luckily, accomplishing this is easy, especially with the continued and expanded use of Microsoft’s Tables.

1. Create a table with a column for “State”, then one for each state listed in the “State” column.
2. Convert this table to an official Excel Table:
   1. On the “Insert” Ribbon, select ‘Table’
   2. If you’d like to rename the table, go to the “Formulas” ribbon and select “Name Manager” in the “Defined Names” Section. I renamed my table “StateTable”.
3. Select the cell where you’ll allow the user to select the “State”
4. On the “Data Tools” Ribbon, select Data Validation/Data Validation; the “Data Validation” window will appear
5. There are three tabs on the “Data Validation” pop-up
   1. Settings: Under ‘Validation Criteria’, select “List” for the “Allow:” option
      1. The “Source:” box will then appear. You can type in the options, separated by a comma; or enter a range of cells that contains the list of values you allow.
      2. NEW NOTE: Now that you’ve created an Excel table, you can also refer to a whole column/field with the following naming convention: INDIRECT(“TableName[ColumnName]”). For our example, I used INDIRECT(“StateTable[State]”).
         
   2. Input Message: Allows you to enter text that appears when the cell with validation is selected
   3. Error Message: Allows you to customize the message that appears if invalid data is entered
6. Select OK
7. Select the cell where you’ll allow the user to select the “City”
8. Once again, on the “Data Tools” Ribbon, select Data Validation/Data Validation; the “Data Validation” window will appear
9. Under the “Source:” box, select “List” and type: INDIRECT(CONCATENATE(“StateTable[“,D2,”]”)), where D2 refers to the cell containing the “State” value. (Note: of course we could have named the cell, D2, to something like “StateField”). If “California” is the selected item in the “State” Field, this formula results in “StateTable[California]”, which will return the list of values in the “California” column/field.
10. Select OK

And that’s it! Just a few steps to making a very useful and user-friendly spreadsheet. Additionally, by using an Excel table, updating the list of items allowed is easy. Adding a row or column immediately outside of the current table, automatically appends to the table. I hope you are able to use this tip. As always, if you have any questions, please don’t hesitate to comment.