Tips & Tricks For The Excel Power User
Excel is a very powerful piece of software but with that power can come complexity. Data validation for cell data entry, controls on user forms or controls placed directly on worksheets often use lists as a data source. If you have a large project with multiple references to those lists maintaining the project can become tedious as the lists change. That maintenance can be much reduced by the use of dynamic lists and dynamic named ranges. With the use of these techniques the controls and dropdowns that reference the lists do not need to be reconfigured when the lists change. This article explains how to set up a data validation using a dynamic list and goes on to look at setting the list up as a dynamic named range that can be referenced by any data validation or control. These principles can be used for controls on spreadsheets, controls on user forms and pretty much anywhere in a project that a list is referenced. I use Excel 2007 and the screenshots are based on that version of Excel but these principles can be applied to other versions with little tweaking.
Creating Our Cell Drop Down Using Data Validation
Imagine you are creating a project where you will need the user to enter a name in a particular cell or series of cells and you intend to use data validation for the entry cells. The names however needs to be from a finite list of predefined names; not just any old names. The problem you have is that the list of names will likely change over time and you really don’t want to keep changing the data validation configuration.
Now select Cell B1 and then select “Data” and “Data Validation” in the ribbon. Select “List” in the “Allow” field and then enter the formula “=$A$1:$A$5” [no quotes] in the “Source” field. Click “OK” and now go and select Cell B1 again.
Changing Our Data Validation To Reference A Dynamic List
What if we could make it so that the list could be edited – Fred could be added if you like – and the dropdown automatically included Fred? The good news is that we can do that quite simply.
Select Cell B1 again and go back to the Data Validation configuration and change the formula in the “Source” field to the following “=OFFSET($A$1,0,0,COUNTA($A:$A),1)” [no quotes]. Go ahead and add another name to the list; maybe Gary. Select Cell B1; our dropdown has changed automatically. Delete a couple of the names and try again. Once more you should find that our dropdown reflects the current list in Column A. How are we doing this? The “Offset” formula is dynamically changing the range of cells used as the data validation list source. That range is being defined by the formula something like this:
- Argument 1 – “$A$1” – this is the initially selected cell – it is the “top left” cell of the range we are defining [though the “top left” cell can be redefined using arguments 2 and 3 – in our case we do not want to do this]
- Argument 2 – “0” – this indicates how many rows to move the selection [the “top left” if you like] down; in this case 0. We want the range to start at Cell A1
- Argument 3 – “0” – this indicates how many columns to move the selection to the right; in this case 0. We want the range to start at Cell A1
- Argument 4 – “COUNTA($A:$A)” – this is the argument doing the grunt work – this argument determines how many rows the range will include starting from the cell defined by the previous three arguments – using COUNTA we are counting how many cells are not empty and setting the range to that many rows – because it is a formula and not a fixed value it automatically updates when a change is made to the list [and yes you are absolutely right this suggests that you should reserve the column for your list only and nothing else]
- Argument 5 – “1” – the same as argument 4 but in this case for the number of columns in the range - in our case it is a single column and there is no need for a formula
Using this technique you can define dropdowns, form controls and the like to dynamically reflect changes to source lists without ever having to make a change to your formulas.
Now: taking this one step further and simplifying our life even more we can use the same principles to set our list up as a defined named range which will make referencing it even easier going forward.
Go back to the ribbon and select “Formulas” and then “Define Name”. Give the range a name that is reflective of the content and easy to remember; in my example I have chosen the name “MyNames”. Now in the “Refers to” field enter the same formula as we used for Data Validation earlier and hey presto you have defined your list to a name. Why would you want to do this? After all the data validation worked fine already. Well let’s say you want to reference the list on multiple sheets, or maybe several different controls in different places will use it. Here is the beauty of this – going forward when you want to refer to the list you can use the name you gave it and not the formula.
Try this. Select Cell B1 again and open the Data Validation configuration again. In the “Source” field remove the formula you inserted earlier and insert the name. In my example “=MyNames”. Try your dropdown again. It still works. What have you achieved? Well – is it not much easier to simply enter the name of the list whenever you want to use it and not have to find or regenerate the formula?
And now for a bonus - there is one other huge benefit to giving your list a defined name particularly if you are working on a large and complex project. It may be that at some point you need to move your list to another column, maybe even to another sheet. If you have multiple references to it in your project based on formulas you will need to find each such reference and edit the formula. If on the other hand you have used a name all you have to do is edit the single formula that defines the name and all references to it will continue to function just as they always have.
One final note; a second bonus of you like. Data validation only functions with a source list that is located on the same sheet within the workbook when referenced using a formula. If, on the other hand, you use a named range that named range can reside on any worksheet within the workbook. You might ask what is so great about that. Well if you have a large project and several different lists locating them all on a single spreadsheet can make administering them easier and there is no chance of the lists getting inadvertently compromised by user actions on the other worksheets? In fact the sheet housing the lists can be hidden and protected to make your lists even safer.
Simon Robson 061611