Custom data validation - formula doesnt evaluate

[dnessim]

Hi all

This has had me stumped for a while.

I have cell M2 containing this formula in it

=COUNTIFS(Indicator_Name,A2,Entry_Date,DATE(YEAR(C2),MONTH(C2),1),Facility,E2,Unit_Group,G2)


If I set a custom validation on cell L2 to this

=M2<=1

If a user trys to enter a value in L2 greater than 1 it will prompt an error. This works.

My question is:

Why cant I put this in the custom validation formula

=COUNTIFS(Indicator_Name,A2,Entry_Date,DATE(YEAR(C2),MONTH(C2),1),Facility,E2,Unit_Group,G2)<=1

 

[Luke M]

When you say "can't put this in", what exactl do you mean? The dialogue won't accept it as a valid formula, or it just doesn't function correctly when you type data into cell L2?

It appears to be a valid formula and *should* work. Might want to make sure your realtive/absolute references are set okay, but that should be an easy fix if need be.

 

[dnessim]

Luke,

Sorry, I am able to enter the formula as my custom validation criteria. I dont get an error.

It just doesnt work, meaning I should get my warning message when I enter values that violate that rule/logic.


So , I should be able to do this?

Dave

 

[Luke M]

You originally stated

If a user trys to enter a value in L2 greater than 1 it will prompt an error.

But that's NOT what the formula does. It just lets you enter data if the COUNTIFS result is less than 1. If it is, you can enter whatever you want into cell L2.

So, do you know for sure that the data in your table is not greater than 1?

 

[dnessim]

Hi Luke,

Sorry for the delay in my reply. I will have to take a look a this again.

I made several attempts at this so I will see if my values are correct.

Thanks

Dave