Creating a NonBlank list for Data Validation


Board Regular
Aug 13, 2006
Here’s an interesting one for the forum.

Firstly, I'd rather not use VBA. Secondly, i have experiemented with Data Validation and have not found an appropriate way to achieve what I want to. If there is a simpler way to achieve it, please let me know. i fear I have overengineered this solution!!

I use a range as the validation list for some dropdown boxes. The range contains formulas with the outcome of “” if false. Therefore, when the dropdown list is selected, there appears many “blank” options scattered through the list; which are the “”s.

I am trying to create a helper column adjacent to the range that will list all non “” values concurrently, and then create a dynamic named range for use as the validation list.

The formula I am using is as follows

=index(A10:$A$25,match(1,(A10:$A$25<>,($B$9:B9)*( A10:$A$25<>””),0))

Entered as an array formula. Note the use of relative and absolute referencing: the range decreases as the formula is dragged down.

What I am trying to get the formula to do is: return the first value from the range that does not equal any previous values (the $B$9:B9 reference, which increases as the formula is dragged down) and does not equal “”.

The formula works until it finds a value after a “” reference (cell B15 -highlighted) It duplicates Fifth-Match even though the cell above it is Fifth match. i.e it appears the first match criteria hasn't worked. Can anyone help me list all non "" concurrently.

Let me know if you require clarification, i appreciate it's hard to write down a lot of contextual background in a post

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Aladin Akyurek

MrExcel MVP
Feb 14, 2002

A10, copied down:


B9 must house a 0.

B10, copied down:




C10, copied down:


The list in column C is defined by:


The link quoted by Brian creates an alphabetical list, which might be more appropriate to take up.

Watch MrExcel Video

Forum statistics

Latest member
renatha prado