Excel data validation is a good tool to employ to direct users to a pre-defined set of choices for a particular cell. You can also allow users to enter their own values. But what if you want to add a set of user-defined values on top of a set of default values, without any scope of entering other values apart from what is in the two lists?

This can be done with a few simple formulas in Excel.

First you need to create a place for users to enter their additional pick-list values. Let’s put these in Column A, from A2 until A11, (we will use the first row for our titles). In column B, we will use a formula to generate a numbered entry, which will follow-on incrementally from our fixed pick-list, in Column E.

We do not need to expect an unlimited number of User-Defined Values, and in this example we will limit the list to 10 entries. It is up to you to decide what is reasonable in your particular case. You may want to use sheet protection to limit editing outside certain areas of the sheet.

The formula used in Column B to generate the entry is:

=IF($A2=””,””,CONCATENATE(ROW()+4,”. “,$A2))

As you can see, the formula displays a generated value if the cell to its left is not empty. Entry 5 is then also added to the pick-list range in Column E.

To do this the cells below the first four fixed entries contain a formula:

=IF($B2=””,0,$B2)

which puts a zero in the cell if there no user-defined value present, with conditional formatting to make text white on white. I use a calculation to count how many items are in the pick-list, as follows:

=COUNTA(E2:E11)-COUNTIF(E2:E11,”=0″)

This formula counts values and subtracts entries which are set to 0 in Column E. This is necessary because otherwise Excel will consider that formulas in Column E are actually entries.

Finally, there is the formula for the Named Range which defines the choices in the pick-list for data validation:

=OFFSET(Sheet1!$E$2,0,0,Sheet1!$I2,1)

Offset will go as far as the last cell in Column E which has an entry.

Advertisements