Who is my Excel Lover?

[kfran84]

If I remember correctly, one of my Katies is an excel lover. I'm an advanced user (accountant) and I'm having trouble writing a formula to get what I want... 

I have 3 columns with state codes (like OK, TX, etc) and I want to make sure nobody put something stupid in like "ZZ" or "XT" or something else invalid.

I thought about doing a vlookup and having it return false if not found in my state code list... or I keep finding references to using "Data validation" and I swear I just tried that, but it did nothing. It didn't catch my "ZZ" as incorrect. 

Ideas? 

[mskatiep]

Not I.... To advanced for me lol

[kellykinns]

I'm mulling this over. however quick and dirty I'd format the cells to highlight duplicate entries. in theory randos wouldn't be duplicated so wouldn't be highlighted. in theory.

this would be under conditional formatting. I could walk you through this pretty quick if you wanted. I just have to get Doug down first.

[kellykinns]

also there may be a way to do true/false thing. like "if one of these than true, if not false" but that would take more setting up than I could walk you through off the top of my head.

[kfran84]

kellykinns, I think I could build a compound IF statement with the state codes we use bc there are only six of them but my boss wants me to do every state. Why? Who knows? So it would be 150 arguments long. For 10,000 lines...

Duplicates is a good test, but we use filtering now to catch the same way. Like, we filter them and manually look at them to see if there's an invalid code.

Your conditional formatting idea might be something I'll look at though.

Thanks for brainstorming with me!

[kellykinns]

If it gets too complicated, you almost want to do it through Access or something. At my last job I had to do lots of complex filtering etc and I always found Access easier to use for that, but maybe I'm a weirdo.

[tallblonde]

Just a reminder that I need to up my excel game. My work BFF told me she literally could not imagine life without excel...I guess I'm doing something wrong?