Using UDFs in Data Validation List

[RatExcel]

Hello,

I've created UDF that returns array of unique values from selected range.
It works perfectly when entered in worksheet cell. When I hit F9 it displays all unque values as array.
I wanted to use this function as source for Data Validation List but I get error "A named range you specified cannot be found"
When I go to Name Manager and add new named range and then try to put it as source in Data Validation I get error "The Source currently evaluates to an error. Do you want to countinue?".
Can anyone help me with this? Is it posssible to apply such function in Data Validation?

 

[RoryA]

Is it posssible to apply such function in Data Validation?

Unfortunately no. You can only use an actual range or a literal comma delimited string (i.e. not the result of a formula of any kind).

 

[RatExcel]

Great, I didn't know about comma delimited string option. So basiclly I can wrote some on change event code that when change in specific range occurs it will trigger my UNIQUE function for that range, convert the result array into comma delimited string and using VBA apply it as data validation formula source. Sounds like an option.

 

[jim may]

Check out the below Example...

Before running Macroname: Update_DV Select cell A1 - go to Data Validation - Select Allow: List, Source: MyTrio

then proceed... Hope this helps... Jim

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L
MyTrio
Peter
Paul
Mary
Paul
Mary
Peter
Paul

<TBODY>
[TD="align: center"]1
[/TD]
[TD="bgcolor: #ffff00, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</TBODY>

Sheet1

​

Sub Update_DV()
    sLr = Range("D" & Rows.Count).End(xlUp).Row
    Range("G:G").ClearContents
    Range("A1").ClearContents
    Range("D1:D" & sLr).AdvancedFilter Action:=xlFilterCopy, CopyToRange _
        :=Range("G1"), Unique:=True
    With Range("G1")
        .Select
        .Delete Shift:=xlUp
    End With
    dLr = Range("G" & Rows.Count).End(xlUp).Row
    On Error Resume Next
    ActiveWorkbook.Names.Add Name:="MyTrio", RefersToR1C1:="=Sheet1!R1C7:R" & dLr & "C7"
    On Error GoTo 0
    
End Sub

 

[snb_]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Columns(Target.Column).SpecialCells(2).AdvancedFilter xlFilterCopy, , Cells(1, 100), True
    Cells(1, 100).CurrentRegion.Name = "snb_000"
    
    Cells(20, 1).Validation.Add 3, , , Join([transpose(snb_000)], ",")
    Cells(1, 100).CurrentRegion.ClearContents
End Sub

 

[jim may]

Corectify from Jim May. Source below requires an "=" sign before the range name.


Allow: List, Source: =MyTrio

 

[RoryA]

Just bear in mind that if you use a string, you're limited to about 255 characters.

 

[snb_]

That limitation only applies to the saving of the validation list.
The next time you open the file you will have to add the validation rule to the cell again.

 

[RoryA]

True - as long as you remember to clear them or are ok with risking a corrupt workbook, you can use a longer string. Using a range is easier.