Excel Bites - Self-Sizing Validation Lists Published 2nd Oct 2014

Excel Bites is an occasional column devoted to useful and simple tricks and techniques that as an Excel user you might well have seen before, but if not you really ought to hear about.

Data validation is a very long-standing feature of Excel, helping people that create spreadsheets for their own or others’ use later to control and check input cells, reducing errors and making sheets easier to use. You can cause a message to be shown when a cell is selected, or when a bad value is entered, but the most important feature is that you can set a rule which defines which values are allowed in the cell.

The most used type of validation is “List”, which allows me to allow only one of a specific list of values to be selected. This can be a fixed list of values, shown below as you would enter it into the settings box, and also how the dropdown then appears attached to the cell.

This is fine until you want to have more control over what values are available, without needing to go back into the settings box every time. Helpfully, the selection button (circled in red) allows you to pick a cell range to contain the allowed values, like this:

Changing the values in the cells C8 to C11 will change what appears in the validation list, without the need to go back into the settings box, and those cells could equally contain formulas whose values could be calculated based on other data entered elsewhere, or on the current date or time.

This is great, but what if I want to add a fifth option? I can’t do this without going back into the settings box and changing the Source range to be C8:C12 instead of C8:C11:

In order to avoid having to change the validation setting later, if I’m creating a spreadsheet with validation in, I’d be tempted to define the Source as a longer list than the number of values I’m currently allowing, so that someone later could add more possible values. The problem with this approach is that even blank cells still appear in the dropdown list, so my dropdown suddenly looks a lot less professional:

So what’s happened to the dropdown? Well, the validation list is now 26 cells tall, leaving room for me to add any or all of the remaining letters of the alphabet as possible values later. Unfortunately, because the cell is currently empty, Excel has scrolled down the list and pre-selected the first blank value in the list. All we can see are the blanks below, and in order to find the valid values I need to scroll the list back up. Chances are a lot of people using your sheet wouldn’t realise this and instead would just think it was broken.

Now, clearly I wouldn’t be writing this if there weren’t a neat solution, so here goes: The validation source box doesn’t just have to contain a list of values or a cell reference, it can actually contain a formula, and by using the function COUNTA (which will tell me how many cells in a range are not empty) and OFFSET (which can shift or resize an input range), I can make sure that the validation source ignores the blank cells underneath:

The formula in the validation source now says (in vaguely plain English):

Starting from the range C8:C33, use OFFSET to move it down by 0 rows and across by 0 rows, then resize it to as many rows tall as COUNTA tells us there are non-blank cells in C8:C33, and one row wide.

In this case, because there are five values filled in, the result of the formula is the range C8:C12, so exactly as we wanted there are no blank cells in the dropdown. Without the need to change the validation settings, I can now have as many as 26 possible values, and have the dropdown list resize itself automatically to the correct length with no blanks.

blog comments powered by Disqus

HTI Labs - Home

HTI Labs is a software and technology consultancy company specialising in software innovation. Whether it is Big Data technology or a fresh approach to solving your business problems, we are the people to speak to.