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. Read more of this post