Nested COUNTIF / IF formula for data validation workaround

I have a table with several columns for end user entry. Because I'm using formulas in the cells, I can't use data validation (nor VBA), but I still want to let end users know if they've entered data correctly or not, so I created an additional helper column, col. W, to check their entries.

There are 10 questions in Part 1, columns I-R (Q1-Q10), and an addition 4 questions in Part 2, columns S-V (Q11-Q14). The trick is the in column G asks if they are answering only the first 10 (Part 1), and all 10 must be either 'y' or 'n', or if they are answering all 14 questions, the last four answered with either, 'y', 'n', or 'n/a'.

FYI, I have an additional column H (All Yes) which will automatically put y's in questions 1-10 or 1-14, depending on the entry in col. G. So I'm looking at a rather complex, nested IF and COUNTIF formula for column W, that will return either "OK" or "Error." It should return 'OK' if all 14 questions are being answered (no blanks) AND if Q1-Q10 are all answered either y or n AND if Q11-Q14 are answered y, n, or n/a. If there is a blank OR if there is an value other than y or n for Q1-Q10 or a value other than y, n, or n/a for Q11-Q14, then it should return "Error."

In my sample, rows 2-4 should be "OK" in column W, and "Error" should be returned in rows 6 & 7.

sample 1a.xlsx

I think I have been making it too complicated - when I think this will work:

Formula:

Please Login or Register  to view this content.

...but feel free to correct me if I'm wrong or come up with another solution!

Because I'm using formulas in the cells, I can't use data validation

why not? Have you tried to?

oops sorry, for some reason I saw DV but read Conditional Formatting - DONT ask how I managed that LOL

Come to think of it, why cant you use CF to show an error? You could make the cell change color if the answer is not what was expected

I have never used that, but the best way to find out is to try/test it.

Thanks for the feedback

Thats great, thanks for getting back to us

For complex CF rules, I often create the rule IN the worksheet. It is easier/quicker to test and adjust there, and like you showed, all you need is a formula that will return TRUE (or 1) or FALSE (or 0)