Category Archives: data validation

#Excel Data Validation – Non-Contiguous Ranges and Changing Data Validation List after Picking

I recently saw this challenge for creating a data validation list from 2 non-contiguous ranges.

Then, while looking up current information about data validation tricks, I reread this post on Debra Dalgleish’s Excel site, which showes a way to change the data validation list based on items picked.

I decided that I would try to combine both of these techniques, while at the same time creating the required data validation list without the need for helper columns. When I started on this, I was not sure that it would be possible, but that is the kind of challenge I like 😊.

I had previously published a method for combining non-contiguous ranges into a comma-delimited string.

Using this technique along with modifying the ranges to exclude blank values, the following formula produces a delimited string combining the elements of two ranges named List1 and List2, as shown in the figure.


TJ_TLists =TEXTJOIN(“,”,TRUE,IF(ISBLANK(List1),””,List1),IF(ISBLANK(List2),””,List2))

It is important to note here that any number of ranges (rectangular, non-contiguous or 3D) can be combined in this step to afford the data validation list in the final step. As an example, see:

The next formula converts this delimited string into an array.

CombinedDV =TRIM(MID(SUBSTITUTE(TJ_TLists,”,”,REPT(” “,999)),ROW(INDIRECT(“1:”&LEN(TJ_TLists)-LEN(SUBSTITUTE(TJ_TLists,”,”,””))+1))*999-998,999))

Unfortunately, an array cannot be used directly as a data validation list. But, since there is more work to do to create data validation that can be used as a pick list, the following formulas are needed.


affords {“”;”b”;”c”;”d”;”e”;”f”;”g”;”h”;”I”;”j”;”k”;”l”;”m”;”n”;””}


affords {“”;2;3;4;5;6;7;8;9;10;11;12;13;14;””}

Then in cell H2 is entered the formula =INDEX(MatchArr,SMALL(MatchRow,ROW()-1)),which is filled down until a formula returns an error. This is the range to be used as a data validation list.

As shown in the figure, the range where data validation is applied


contains an β€œa” and an β€œo”.

Then, the formula used for the data validation list is


So, when the data validation is used in its current state, the list will not contain those two letters.

I hope that you find this useful.

The example file can be downloaded here.