excel custom data valadation with countifs

[Alwinaz]

Hi there

I posted a thread earlier it looks lik it was wrorking but it now appears not working.

Can you use countifs in custom data valadation my formula that i want to use is =COUNTIFS($B$4:$B$50000,"="&B4,$AO$4:$AO$50000,"="&AO4)=1 Can any body help me?

 

[Andrew Poulsom]

In what way isn't it working exactly?

 

[Alwinaz]

What i am trying to do is to prevent the user to enter a claim number in column b more than once a day. So in column b the user will enter the claim number. once the claim number is entered i want data valadation to check if this claim number already exist for this date (column AO), if so it must give a error message. The problem is even if i enter a unique number it gives me the error message.

 

[Andrew Poulsom]

The formula works for me. Are you sure that you have applied the validation to the correct range (B4:B50000 with B4 the active cell)? if you put the formula in a spare column on your worksheet does it return what you expect?

 

[Peter_SSs]

Also, in the DV dialog, Settings tab, make sure that you have not removed the tick from "Ignore blank"


BTW, you don't need those "=" in the formula.
=COUNTIFS($B$4:$B$50000,B4,$AO$4:$AO$50000,AO4)=1

 

[Alwinaz]

is it perhaps possible that it is not working due to the fact that i have a vba that time stamp the cell in ao once cell b is updated?

 

[Peter_SSs]

For your Data Validation in column B to work, the date would need to be in column AO before the value is entered in column B. Alternatively, you could build the data validation into your time stamp code.

 

[Alwinaz]

i am using this VBA to date stamp. I can not write VBA's but i can modify them to work. how will i change this vba to include the data validation?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)


If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub
For Each C In Intersect(Target, Columns("B:B"))
    If C.Value <> "" Then
        C.Offset(0, 38).Value = Format(Now, "dd-mmmm-yyyy h:mm")
    Else
        C.Offset(0, 38).ClearContents
    End If
Next C

End Sub

 

[Andrew Poulsom]

That code is writing to column AN not AO. Also, it's writing the date and time, so there are unlikely to be duplicates.

 

[Alwinaz]

in column ao i have the value of AN as i had problems with the value as inserted in AN in other formulas.