Dependent Validation Type

Data Validation Settings in B1:

Allow: Custom
Source: =IF(A1="Number", ISNUMBER(B1), IF(A1="Text", ISTEXT(B1)))

You're putting too many apples into this cart. I don't believe you can accomplish all of that with mere formulas and DV setups. You would have to employ VBA programming on your sheet to dynamically rewrite the DV settings in column B based on what is selected in column A.

If you'd like help with some VBA, I can move this thread to the VBA forum and we can continue there.

This macro will accomplish your requirments:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

Application.EnableEvents = False
On Error GoTo ErrorExit

For Each cell In Intersect(Target, Range("A:A"))
    Select Case UCase(cell.Value)
        Case "TEXT"
            With cell.Offset(, 1)
                .Value = ""
                .NumberFormat = "@"
                With .Validation
                    .Delete
                    .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Formula1:="=ISTEXT(B" & cell.Row & ")"
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = "This cell must contain a text entry"
                    .ShowInput = True
                    .ShowError = True
                End With
            End With
        
        Case "NUMBER"
            With cell.Offset(, 1)
                .Value = ""
                .NumberFormat = "0.#"
                With .Validation
                    .Delete
                    .Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="0", Formula2:="32.99999999999"
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = "This cell must contain a number between 0 and 32.999999999"
                    .ShowInput = True
                    .ShowError = True
                End With
            End With
        
        Case "PERCENTAGE"
            With cell.Offset(, 1)
                .Value = ""
                .Style = "Percent"
                With .Validation
                    .Delete
                    .Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=".1", Formula2:=".9"
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = "This value must be a percentage 10%-90%"
                    .ShowInput = True
                    .ShowError = True
                End With
            End With
        
        Case "LONG"
            With cell.Offset(, 1)
                .Value = ""
                .NumberFormat = "@"
                With .Validation
                    .Delete
                    .Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="1", Formula2:="150"
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = "This message is limited to 150 characters"
                    .ShowInput = True
                    .ShowError = True
                End With
            End With
        
    End Select
Next cell

ErrorExit:
Application.EnableEvents = True
End Sub

To add to your workbook:

1. Open up your workbook
2. Right-click the sheet tab and select VIEW CODE
3. Copy and Paste in your code (given above)
4. Get out of VBA (Press Alt+Q)
5. Save as a macro-enabled workbook

The macro is installed and ready to use. It operates on its own. As you make drop down changes in column A, it will apply custom DV settings to column B on that row to match your choice.

Hi JB,
Thank you very much for this. I suspected there would be some VBA involved to accomplish this.
I will give this a shot and let you know how it goes.
Many thanks

Hi JB,

I have been testing your suggestion and indeed this seems to be almost done on your example. What I found missing is the option for a drop down with a list of set values.
Can this be added? I have tried modifying your code with ".Add Type:=xlValidateList" but to no avail. Also on my production spreadsheet the validation reference cell is on column "AR:AR" instead of "A:A" and the cells that required validation are on column "Z:Z" instead of "B:B".
I understand that this requires some slight modification on your code but i do not know on which point exactly.
In any case i Have attached the code i came up with for your reference.
Thanks in advance.

 Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

If Intersect(Target, Range("AR:AR")) Is Nothing Then Exit Sub

Application.EnableEvents = False
On Error GoTo ErrorExit

For Each cell In Intersect(Target, Range("AR:AR"))
    Select Case UCase(cell.Value)
        Case "List"
            With cell.Offset(, -16)
                .Value = ""
                .NumberFormat = "@"
                With .Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Indirect(AS" & cell.Row & ")"
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = "Please select from drop down"
                    .ShowInput = True
                    .ShowError = True
                End With
            End With
        
        Case "Number"
            With cell.Offset(, -16)
                .Value = ""
                .NumberFormat = "0.00"
                With .Validation
                    .Delete
                    .Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="-10", Formula2:="10"
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = "Please enter a value between -10 and 10. Decimals are allowed"
                    .ShowInput = True
                    .ShowError = True
                End With
            End With
        
        Case "Number 0 to 100"
            With cell.Offset(, -16)
                .Value = ""
                .NumberFormat = "0"
                With .Validation
                    .Delete
                    .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="0", Formula2:="100"
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = "Please enter a value between 0 and 100"
                    .ShowInput = True
                    .ShowError = True
                End With
            End With
        
    End Select
Next cell

ErrorExit:
Application.EnableEvents = True
End Sub

Tip, turn on the macro recorder, then let it record you applying the data validation LIST to a cell, then turn off the macro recorder. The resulting code should give you snippets and syntax you can adapt into your permanent macro.

I changed the cell.Offset(, -16) to a style of syntax that is much less cumbersome to read. I also adapted the NUMBER option to allow decimals, you stopped the range at -10 and 10 which would not have allowed decimals ON the -10 and 10 values.

Lastly, in the code we use the UPPER CASE version of the items in the initial drop down, just to be exact.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

If Intersect(Target, Range("AR:AR")) Is Nothing Then Exit Sub

Application.EnableEvents = False
On Error GoTo ErrorExit

For Each cell In Intersect(Target, Range("AR:AR"))
    Select Case UCase(cell.Value)
        Case "LIST"
            With Range("Z" & cell.Row)
                .Value = ""
                .NumberFormat = "@"
                With .Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=$AZ$1:$AZ$10"    'Range to list
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = "Please select from drop down"
                    .ShowInput = True
                    .ShowError = True
                End With
            End With
        
        Case "NUMBER"
            With Range("Z" & cell.Row)
                .Value = ""
                .NumberFormat = "0.00"
                With .Validation
                    .Delete
                    .Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="-10.9999999", Formula2:="10.9999999999"
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = "Please enter a value between -10 and 10. Decimals are allowed"
                    .ShowInput = True
                    .ShowError = True
                End With
            End With
        
        Case "NUMBER 0 TO 100"
            With Range("Z" & cell.Row)
                .Value = ""
                .NumberFormat = "0"
                With .Validation
                    .Delete
                    .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="0", Formula2:="100"
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = "Please enter a value between 0 and 100"
                    .ShowInput = True
                    .ShowError = True
                End With
            End With
        
    End Select
Next cell

ErrorExit:
Application.EnableEvents = True
End Sub

Thank you very much JB. And thanks for the tip.
You have been a great help.
I will go ahead and do some more testing but I think I am almost there.
One thing I noticed while testing this is that these rules are not applied if the reference for the validation is the product of a function instead of a user selection.
To be more specific if we select "Number" on a cell in AR:AR column then the code works. However if the value on the cell in the "AR:AR" column is the product of a vlookup that reads "Number" then the validation is not applied.
Is there any way to remedy this?
Again you have been extremely helpful thank you for taking the time to explain these things to me.
If you feel this post will be better suited in the VBA section please go ahead and move it there.

Friend, please stop using the REPLY WITH QUOTE button. Use the REPLY button. Nice and clean thread. Use a the quote button if you wish to discuss something specific that's been said, and even then edit it down to just that part. OK? No demerits cause you're new, but do take a moment to read the Forum Rules in the menu bar above, it is very helpful and instructive. Thanks.


Yes, you are correct. There are many different types of events that can be put into the Sheet module, and the Worksheet_Change event is triggered by a manual change to any cell on the sheet. I do not recommend the Worksheet_Calculate event which would trigger any time any value changed on the sheet including formulaic values because it is not "Targeted" the way the change macro is, so you would have to restructure the macro to examine every cell in column AR and it would most likely lead to a noticable slowing down of your sheet's performance.

We can do that, if formulas are a must-have, but I predict this will be more cumbersome. Hmm... maybe we could use TWO columns, AR as the one that is being checked, and another column off to the right like AZ where we store the current values in AR, then the worksheet_calculate event goes down AZ and only acts on the row(s) where the value no longer matches AR, acts, then updates AZ so it matches again?

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thanks.