I avoid the use of Volatile Functions, especially OFFSET, which is commonly used to update a list or range. They can slow down the operation of your workbook. For very large workbooks with lots of data, it can be significant and irksome.
Worksheet cells that use Data Validation for a drop-down list can simplify the input process, or be used to limit the available choices. But the list needs be expandable. Here are two primary ways to keep your data validation list automatically updated, without having to resort to using the OFFSET function.
Update Your List Range with VBA
Put your data validation drop-down cell on one worksheet and the reference list range on another worksheet. Example: Sheet1 contains a cell with data validation. Sheet2 contains a data range (the list) that is given a defined name of myList. Add some VBA code in the Sheet2 Deactivate routine to update the named range.
Private Sub Worksheet_Deactivate()
Dim rng As Range
Set rng = Sheet2.Range("myList").CurrentRegion
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
rng.Name = "myList"
This is an event-based programming technique, which I commonly use with Excel 2003.
Use Some Table INDEX Magic
This is a variation of the same thing, but no VBA programming is warranted. Instead, use a Table for your reference list data. Then create a defined name with the INDEX Function, and use that name for the data validation list.
Tables automatically update their ranges when expanded and the INDEX function will too. Example: Create the defined name myListFormula and use =INDEX(Table1,0,1) as the formula. Then when setting the data validation list, use =myListFormula as the list reference.
I’ve put together a workbook with the two examples listed above. The first technique, with VBA, uses two worksheets: Lookup 1 and Data 1. The Data 1 worksheet has the VBA code, which updates the named range when deactivated. You can add or subtract to this list and the data validation list on the Lookup 1 sheet will automatically be updated.
Both Lookup sheets have data validation in cell A2, which is a list of names. I’ve added another column for the city that uses a formula to get the right value from the list.
The second example uses Lookup 2 and Data 2 worksheets. The Table is on the Data 2 worksheet. When you add or subtract data from this Table and the defined name myListFormula will automatically update the data validation list on the Lookup 2 worksheet. Be sure to look at the Define Name dialog box (on the Mac) or the Name Manager (Windows) to see the INDEX formula.
Download the file: Data_Validation_List_Update.xlsm
Since there is no OFFSET function, updating at random intervals, in either of these examples, I can rest easy. That’s one less thing to slow down your worksheet.
Michael Pierce says
In the table-driven approach, what do you think about using the table/column naming convention rather than the INDEX formula? For example, myListFormula could be defined as =Table1[Name]. I think the advantage may be that if someone rearranges sequence of table columns, the validation will still work. Are there any drawbacks from this approach?
@Michael Pierce, it’s funny you should ask about using Tabel1[Name]. I tried to use that directly for the Data Validation list, but Excel 2011 gave me an error. Next I tried to create a defined name with =Table1[Name], but that didn’t take and I had to get rid of the equals sign before Excel would accept it.
Next I tried to use this newly defined name for a Data Validation List and got the following error: “The List Source must be a delimited list, or a reference to a single row or column.” So I went with INDEX, because it works.
However, using Table1[Name] should have worked, both in the raw state and as a defined name, in the Data Validation list. I’m not sure why this failed, but it seems to be either an error or oversight with Microsoft Excel 2011. I didn’t try this with any of the Windows versions (I will of course) so it might just be particular to the latest Mac version of Excel, which wouldn’t be uncommon.
Michael Pierce says
Ahh…makes sense. In Excel 2007 on Windows, I wasn’t able to use Table1[Name] in the Data Validation List itself, but I was able use it in the Defined Name. So it must be a Mac thing.
Jerry Bailey says
I’ve looked all over for a solution to a slightly different problem. I need to dynamically limit a list in a different way. A user selects a department from a drop down list (no problem – data validation works fine from a list in another sheet). Once the user has selected a department, I want the next cell to be a drop down limited to the accounts for that department. Another sheet lists valid account numbers for each department. I can’t create separate lists for each department – there are 294 of them, and there are over 900 combinations of departments/accounts. How can I create a lookup table “on the fly?” I normally do this stuff in Access, but a client wants it in Excel.
I wrote an article on that entitled, A Dynamic Dependent Drop Down List in Excel. Here’s the link.
Alex H says
I’m not entirely sure what’s going on with the INDEX piece, but I believe I know an easier way to make a Data Validation List from a named Table.
In the Data Validation menu, select List, and then type =INDIRECT(“Table1[name]”). The table will automatically expand when items are added and the data validation uses the table name to find the list. This only works when the table is one column wide.
I vaguely remember doing this in tables with more than one column, but I’m having trouble locating anything on this and can’t remember which project I did it on. I can’t find the format for indicating which column of a named range.
INDIRECT works, but is slightly worse because I can’t change the “name” part of the formula automatically. This technique works on a Table with more than one column because the “name” you refer to is simply the column heading. Enter the right column heading and the INDIRECT function will return a list when used with Data Validation. But as I’ve said, it’s difficult to change the “name” value because it is a text.
The INDEX function works in much the same way. =INDEX(Table1,0,1) returns the entire data in column 1 of Table1. (Look in the Help documentation for the INDEX function and you will see that when a zero (0) appears in the row argument, all rows are returned.) I can then create a defined name, lets use “myList” and enter the aforementioned formula =INDEX(Table1,0,1). Now you can create a Data Validation list that is =myList and the entire first column will be returned.
You can then insert the MATCH function in place of the (1) value to lookup the location of a particular column heading, based on another cell, and you have a dynamic Data Validation list.
There is a great article entitled The Imposing INDEX that is simply one of the best article ever written for Excel functions. Check it out.