I have to find a way to use multiple Data Validations in a single cell. Currently, I'm simply validating newly entered data against the contents of another column on the same row to see if it's identical. If it is not, all is ok, if it is not I issue a warning message and wait for user response. The most basic kind of validation. (=S=Q) with (S) being a Budget Amount and (Q) being the Actual Amount.
I need to expand that validation with a test to see if that value (S) appears anywhere else on the sheet in conjunction with the same Provider (A). Essentially if the Actual Amount (Q) is not equal to the Budgeted Amount (S)or the combination of (Provider (A)and Actual Amount (S))already exists in the sheet.
Essentially if (Q=S) or (A&S) pre-exist anywhere prior it is a potential error. I've tried creating an array of sorted, unique combinations of A&S and validating against that but can not find a way to get Data Validation to accept both the validations.
Please, any help, ideas or reading would be a great assistance. My already balding head is getting worse!