Formula for validity

[Tytalus]

I was in a situation where I had to do some conditional formatting for cells that had invalid entries relative to validation rules.

As far as I know there's no formula that checks validity, so I started a thread on the official Excel threads, and I talked a bit about it on twitter, but have gotten no responses.

So now I'm wondering if there's already a way to do this that I'm missing (other than using "Circle Invalid Data").

https://excel.uservoice.com/forums/...34-formula-for-checking-validation-compliance

Am I barking up an already "solved" tree?

 

[baitmaster]

There's no formula for checking Data Validation rules

You could potentially use VBA to look at the DV rules, but I wouldn't bother. If it was that important to me I'd find another way of preventing people entering the wrong values - e.g. by using a User Form, or a Worksheet_Change event

For example as you noted, DV can be ignored by Copy > Paste values. However you could use Worksheet_SelectionChange event to disable cutcopymode if Copy is active and you select the range in question. A simple formula that gives a big red error message should usually suffice

 

[Tytalus]

Yeah, I was thinking I could solve this pretty easily with vba but I can't run vba on the form in question.

The problem is, I've got a lot of copy and pasting going on (and it's good - my users' lives are made much easier with it) but pasting values into cells bypasses validation (and any validation alerts). So I was looking for a way to highlight the invalidity so the user can be alerted.

 

[Tytalus]

I ended up creating a CF instance checking for validity for each validation area - but since each validation rule was unique, it was a huge PITA. (Plus I have more CF objects and we know how that goes). So I was thinking of how useful a "check validity" formula could be. We know under the hood they already have this functionality because of the circle option.