Data Validation Formula not working

Hi all,

I have a Data Validation formula here that I believe should be working:

=OR(NOT(OR(LEFT(R8C3,2)="P-",LEFT(R8C3,2)="S-",LEFT(R8C3,2)="C-")),(COUNTIF(Clients1,RIGHT(R8C3,6))+COUNTIF(Clients2,RIGHT(R8C3,6))+COUNTIF(Clients3,RIGHT(R8C3,6)))>=1)


To try and explain what I am doing here, it is essentially a two stage variation on cells in the range R3C8:R12C22

• Stage 1: Check if the Cell begins with "P-", "S-" or "C-"
• If it doesn't, pass the Validation
• If it does, Look at the Right 6 Characters of the cell and check if these appear anywhere in the three ranges Client1, Client2 or Client3
• if it does, pass the Validation
• Otherwise, Fail the Validation

I have a separate cell calculating this formula to give the correct "True" and "False" results, but when I place I it in the Data Validation settings, it doesn't work. Can any help me with why that is the case?

Thanks for your time,

Rich

Hi,

I've attached a version of the file with all of the personal information stripped out. That said, the validation should still work based on the validation in R3C8:R12C22.

Thanks for your time

Rich