Category Archives: data validation

Adding Data Validation to ANY Cell Using an Excel User-Defined Function

 

In previous articles I have written about the connection between Excel UDFs and objects, I have noted that almost any object can be invoked through its use. So, it was with interest that I read a recent article on Debra Dalgleish’s website on the accidental deletion of dropdown arrows in cells with data validation lists when running macros.

https://contexturesblog.com/archives/2018/10/11/missing-data-validation-arrows/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+contextures%2FdCfy+%28Contextures+Blog%29

In the article, she stated

“If you run a macro that deletes shapes on a worksheet, it might also delete the drop down arrow. Excel sees that arrow as a worksheet shape.”

Well, I just wrote this article where a UDF invoked filter arrows to emulate the properties of Excel new FILTER function.

https://dhexcel1.wordpress.com/2018/09/30/the-new-microsoft-filter-and-sort-functions-are-great-but-what-about-the-old-ones-no-one-know-existed/

So, I figured that, just maybe, a UDF could do the same thing with data validation. It CAN! And, it can add the data validation to any desired worksheet cell.

The following code is for the AddValidation VBA UDF:

Function AddValidation(vRange As Range, vList As Range)

On Error Resume Next

Range(vRange.Address).Validation.Add Type:=xlValidateList, _

Formula1:=”=” & vList.Address

End Function

Its use can be seen in the following figures. In the first, the formula

=AddValidation(A3,letters) is in cell F5. Actually, in the figure the 2nd argument in the UDF is J1:J3, but that is equivalent to the defined name range called letters.

AddVal1

The first argument points to cell A3 as the target for adding the data validation list. In the 2nd figure, you can see that the data validation drowdown arrow appears in A3. Pretty amazing!

AddVal2

I think you can see the myriad of possibilties that are possible by using this technique.

Free free to download the sample workbook at the link shown below. HTH!

ValidationUDF

Advertisements

#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.

https://www.sumproduct.com/blog/article/monday-morning-mulling-december-challenge

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.

http://www.contextures.com/xlDataVal03.html

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.

https://dhexcel1.wordpress.com/2017/05/21/excel-short-and-sweet-tip-15-using-textjoin-with-non-contiguous-ranges-by-david-hager/

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.

DV_PIC1

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:

https://dhexcel1.wordpress.com/2017/05/23/excel-native-3d-ranges-with-the-textjoin-function-plus-bonus-by-david-hager/

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.

MatchArr=IF(ISNA(MATCH(ROW(INDIRECT(“1:”&COUNTA(CombinedDV))),MATCH(SelectDV,CombinedDV,0),0)),CombinedDV,””)

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

MatchRow=IF(ISNA(MATCH(ROW(INDIRECT(“1:”&COUNTA(CombinedDV))),MATCH(SelectDV,CombinedDV,0),0)),ROW(INDIRECT(“1:”&COUNTA(CombinedDV))),””)

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

SelectDV=DVSheet!$B$2:$B$16

contains an “a” and an “o”.

Then, the formula used for the data validation list is

DVList=DVSheet!$H$2:INDEX(DVSheet!$H$1:$H$16,MATCH(TRUE,ISERROR(DVSheet!$H$1:$H$16),0)-1)

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.

DV_TwoLists