Excel IF-THEN Data Validation

[miaousky]

Hi there,

I'm a beginner at this, so apologies in advance for incompetence. . .

I have three columns, A, B, and C. Each column has over a thousand values of data manually inputted over time.

Column A can be 0, 1, or 2.

Columns B & C can have values of 99, 1, 2, or 3.

If Column A is 0, BOTH columns B & C need to have values of 99.

Step by step, how would I create a formula and use data validation to identify any spots in the data where A=0 but B&C are not 99.

[judgeh59]

you haven't said what you want to do if it's true....this will return a TRUE or a FALSE...

Please Login or Register  to view this content.

OR

Please Login or Register  to view this content.

[miaousky]

Yeah-- sorry, I don't want to do anything if it's true.

So I want to check the data so that if A= 0, B= 99, C=99-- nothing is marked. Also, I don't need to check any data where A= 1 or 2.

But if A=0 and either B or C don't = 99, I want to circle the invalid data.

[judgeh59]

how setting the row to some color using conditional formatting?....actually circling the data would probably require a VBA solution....

[miaousky]

Okay, yeah that would work. I was trying to do this thru Data Validation which has been hella confusing, but if conditional formatting would work-- that's fine too.

But if I'm correct, the above formula that you noted would mean that EVERY instance where A=0, B=99, C=99 isn't satisfied would be marked. And I want to exclude formatting of situations like A=1, B=2, C=3. I only want to catch situations like A=0, B=1, C=99.

[judgeh59]

you can use this....sorry got a little confused

Please Login or Register  to view this content.

[miaousky]

Thank you--

sorry, but to clarify, I'm really new at this-- where does that go? In formulas? Or should I be using data validation for that?

[judgeh59]

i changed the formula to

Please Login or Register  to view this content.

I put this in the CF area for Column "D"...not knowing what your worksheet looks like, I hope this will work for you...please see the attached file...and check out the CF formulas...

let me know if this works for you....

[miaousky]

Thank you!

[judgeh59]

glad I could help and thanks for the rep points....

If this has been completed to your satisfaction, please set the thread to Solved...