# Data validation formula problem



## XrayLemi (Dec 25, 2022)

Hello all and Happy Holidays.
I know my problem is probably simple and staring me right in the face, but I cannot find it.

I have this Data Validation formula in H14      =G14="Not Listed"
It is simple and straight forward.  It insures that no user can enter a value in H14 unless The value in G14 is "Not Listed"

I now need to create a formula for L14.  However I need it to work for L14  with three different values that come from K14.  The values in K14 can only be:  "Sec." , "Min." , or  "Min:Sec".
These values come from either VBA code or an activex ComboBox choice. 

The second problem is.....  If a user removes any one of the stated values or selects the lone blank value from the ComboBox,  Then I need the Value in L14 to be blank.
Both the validation formulas in columns H and L are in cells 10 thru 5000.

I would post all the failed formulas that I have tried, but that would take all day.  (both to type and to read!)

Thank you in advance,
Jim


----------



## rlv01 (Dec 25, 2022)

XrayLemi said:


> I now need to create a formula for L14. However I need it to work for L14 with three different values that come from K14. The values in K14 can only be: "Sec." , "Min." , or "Min:Sec".
> These values come from either VBA code or an activex ComboBox choice.


Here's one  VBA implementation.


```
Sub AddvalidationToCellL14()
    With ActiveSheet.Range("L14").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Sec,Min,Min:Sec"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub
```


----------



## XrayLemi (Dec 25, 2022)

Hello,
 Thanks for the quick reply.
I must have not been clear in what I need.  The input is already in K14 done with either VBA or an activex ComboBox.  What I need is a formula like the picture shows.  This will allow data entry into the cell only if the criteria is met.  If the criteria is not met, data cannot go into cell L14.  Also if the criteria is removed from cell K13, so is the data in L14.  The data that will be typed in L14 is only a number.


----------



## Akuini (Dec 25, 2022)

XrayLemi said:


> I now need to create a formula for L14. However I need it to work for L14 with three different values that come from K14. The values in K14 can only be: "Sec." , "Min." , or "Min:Sec".
> These values come from either VBA code or an activex ComboBox choice.


Try using OR:

```
=OR(K14="Sec.",K14="Min.",K14="Min:Sec")
```


----------



## XrayLemi (Dec 25, 2022)

Hi Akuini,
That was one of the first Things I tried.  
In between posts, I found a suitable answer.   I used this formula    =NOT(ISBLANK(K14))  
However it did not work until I removed the check in the ignore blank box.  The picture shows what I did.


----------

