Data Validation Custom Function along with List

[vijay.vizzu]

Hi...All,


I just want to use Data Validation List function along with Custom function becoz, In list function we can validate specified data as a list, & custom we can use any formula to retrive the data, can it be possible to use both functions at a time?


Any help would highly appreicated.

 

[NARAYANK991]

Hi Vijay ,


I think you cannot choose Data Validation using a List and a Custom function at the same time , from the in-built dialog box.


However , what you can do is use whatever formulae you want to build up a list , and use that list for Data Validation.


Narayan

 

[prasaddn]

Hi,


Not able to interpret your requirement, can you try to elaborate more, please.


Regards,

Prasad DN.

 

[SirJB7]

Hi, vijay.vizzu!

I agree with NARAYANK991, you have to choose only one validation method, so if you need involving a UDF, just do all the stuff there: load the list (or check manually against it) into your function, and perform specific additional validations.

Regards!

 

[vijay.vizzu]

Thanks to all to share your opinion, now i want to elaborate my requirement so that you people can give me better suggestion, I have just put one validation "EXACT(A1,UPPER(A1))" this formula checks what ever we enter in A1, it should be capital, it works fine, but i just want to add validation of words i.e 'amortize' & capitalize . how...?


thanks in advance

 

[SirJB7]

Hi, vijay.vizzu!


Try doing this:

a) Set a named range called "DataValidationList" in C1:C2, with "AMORTIZE" in C1 and "CAPITALIZE" in C2.

b) Copy this code in the VBA section of the sheet


-----

[pre]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bOk As Boolean, I As Integer
Application.EnableEvents = False
With Target
If .Address = "$A$1" Then
bOk = False
For I = 1 To Range("DataValidationList").Count
If .Value = Range("DataValidationList").Cells(I, 1).Value Then
bOk = True
Exit For
End If
Next I
If Not (bOk) Then
MsgBox "Input error", vbApplicationModal + vbCritical + vbOKOnly, "Warning"
Application.Undo
End If
End If
End With
Application.EnableEvents = True
End Sub

[/pre]
-----


Regards!

 

[NARAYANK991]

Hi ,


If you mean that only the two words AMORTIZE and CAPITALIZE can be entered in A1 , why not choose List from the Data Validation dialog box , and enter the two words separated by a , ( comma ) ?


Narayan

 

[Luke M]

You use the custom formula, but define the list within formula like so:

=AND(EXACT(A1,UPPER(A1)),OR(A1={"AMORTIZE","CAPITALIZE","CREDITIZE","UNIONIZE","etc"}))


Curious though, why does user need to make sure entry is in upper case? I would propose either using the UPPER function in whichever formula takes A1 as INPUT, or use the UCase operator if using VB.