Excel Data Validation Holy Grail

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:

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.