Office challenge: How would you create a dynamic Data Validation range?

Here's the solution to last week's problem with Excel's disappearing validation lists. And continuing the theme, this week's challenge involves creating a dynamic validation range.

In the solution part of this week's challenge, I'll show you how validation lists can seemingly disappear. It's an obscure problem that you might never experience. However, there is a common problem with these lists: The validation range is static. That means that once users have filled the original range, they must extend the range to continue using the drop-down lists. Most users won't know how to do this. Even if you show them how, they probably won't remember when the time comes. A dynamic validation range would solve the problem, but that's not an option, at least not through the available options. So, how would you create a validation range that automatically updates to include new rows? Last week we asked… Where'd my Data Validation lists go? It isn't a common problem, but it can happen — validation lists can be working fine and suddenly disappear. Fortunately, they're easy to reclaim. To demonstrate, let's create a simple list and make it disappear. To create the list is easy enough:

  1. First, you need a list of items. Sometimes that list will already exist in the data, as shown below. The first three values in column A make up our list.  If the list doesn't occur naturally in the data, you must enter one in an out-of-the way spot in your sheet.
  2. Select the range where you want to use the validation list. For the sake of this example, select A1:A10.
  3. Choose Validation from the Data menu.
  4. In the Settings tab, choose List from the Allow drop-down list.
  5. Click inside the Source control and type the list's range or click the icon to the right, select the list (A2:A4), and then return to the dialog box.
  6. Click OK.
As you can see (above) this feature supplies a list for users. Instead of typing values, users select an item from the list. This arrangement has two purposes:
  • First, you help the user. Choosing from a list is quicker and easier than typing values, especially when the entries aren't common words or numbers, like the project ID values in this sheet.
  • Second, you help yourself by limiting acceptable values.  Users can't enter inappropriate data or typos, which can mean disaster for sorts and other analytical tasks.
Now, let's make the list disappear. Interestingly, there's more than one way! #1: Hide objects If you hide objects, the validation drop-down arrows will disappear:

  1. Choose Options From the Tools menu.
  2. On the View tab, choose Hide All in the Objects section. (Show All is the default.)
  3. Click OK.
When you return to the sheet, the validation drop-down arrows are gone. Fortunately, getting them back is simple. Repeat the above process, but click Show All in the Objects section. #2: Disable drop-down option The feature itself allows you to disable the drop-down list as follows:

  1. Select the cell or range that has a data validation list. You can disable the drop-down list in just one cell or the entire range.
  2. Choose Validation from the Data menu.
  3. On the Settings tab, uncheck the In-Cell Dropdown option.
  4. Click OK.
If your lists disappear, one of the above settings is likely the cause and you should be able to reinstate the list(s) quickly. In addition, if you create a new validation list, but the sheet doesn't display the drop-down arrows, check these settings.


Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox