Creating select lists in Excel is pretty easy. But creating them so that it’s easy to add new values is not so obvious. Usually you define a range around the list values, and reference the range from the list definition. The problem with this approach is that is you happen to insert a new value at the end of the list, you need to redefine the range. So you need to remember to insert new values in the middle of the list, which depending how you setup your reference lists, might cause other problems.
This little snippet allows you to simply add new values anywhere on the list, including at the end, without having to redefine anything.
- Enter the List Values: Create a separate worksheet to contain the values you want on your lists (in this example we’ll use a worksheet called “ref”). Use columns for each list. Row 1 will be the name of the list, and rows 2 down will be the values. So if you want a list of Priorities:
- Define a Name for the Values: Goto, Insert->Name->Define (or press Ctrl-F3). Enter a name for your values (“priorities”), and enter this formula, changing the worksheet name (“ref”), and column (“A”) to match where you put your values:
=INDIRECT("ref!A2:A" & COUNTA(INDIRECT("ref!$A:$A"))) - Create the List: Highlist the cells you want to define the list on. Goto Data->Validation, and on the Settings tab, for Allow, select “List”. For Data enter the name you gave to the value-list, in our case
=priorities. Make sure you have “Ignore blank” and :In-cell dropdown" checked.
That’s it. Now if you want to add new values to the list, go ahead and do it on the “ref” worksheet. The select list will always show any new values you add. You don’t have to redefine the range you specified.