The previous two posts have charted my experiences and the techniques I have put together in the context of Excel data validation. The ultimate objective was to create a pick-list which would contain both a standard set of default values and any user entered values together in a single list. This Holy Grail of data validation had to be achieved, in addition, without the use of macros.

The ultimate solution relies on a complex combination of formulas on a hidden sheet. Here is the step-by-step process described in detail:

1. Create a new worksheet which will ultimately be hidden from the user, my example is called Drops.

2. Create a dynamic named range called DropSource on the column which will contain your data validation.

=OFFSET(‘Data’!\$A\$2,0,0,COUNTA(‘Data’!\$A:\$A)-1,1)

3. In A2 of Drops, create an array formula using Control-Shift-Enter with the following form:

=INDEX(DropSource,MATCH(0,IF(ISBLANK(DropSource),1,COUNTIF(A\$1:\$A1,DropSource)),0))

4. Drag to copy this formula down for the number of rows equivalent to the maximum number of unique values you expect to have in your pick-list.

5. Create a dynamic range on column A (in my example called Good) to pick up only values and exclude #N/A

=OFFSET(Drops!\$A\$2,0,0,COUNTA(Drops!\$A:\$A)-COUNTIF(Drops!\$A:\$A,”=#N/A”),1)

6. Create your list of default values somewhere, for example in column B of Drops and give the range a name, in my example this range is called Fixed.

7. In column C, row 2, enter the following as an array formula

=IF(ROW(A1)<=COUNTA(Fixed),INDEX(Fixed,ROW(A1)),IF(ROW(A1)<=(COUNTA(Fixed)+COUNTA(Good)),INDEX(Good,ROW(A1)-COUNTA(Fixed)),”0″))

then copy it down the same number of row as you did in column A.

8. Next create a dynamic named range on this column, (in my example called GoodFinal), which will concatenate unique values entered in the column with data validation plus the list of default values and remove zeros.

=OFFSET(Drops!\$C\$2,0,0,COUNTA(Drops!\$C:\$C)-COUNTIF(Drops!\$C:\$C,”=0″),1)

9. In column D, row 2 enter the following array formula

=INDEX(GoodFinal,MATCH(0,IF(ISBLANK(GoodFinal),1,COUNTIF(I\$1:\$I1,GoodFinal)),0))

and copy it down the required number of rows.

10. Finally, you are ready to create a dynamic named range on this column, which will furnish the final list of values for your drop-down on Data!\$A

=OFFSET(Drop!\$D\$2,0,0,COUNTA(Drops!\$D:\$D)-COUNTIF(Drops!\$D:\$D,”=#N/A”),1)

One final constraint is that you will need to enter a first value in your first cell which uses your drop-down, which could be an instruction, such as Enter values here.