Cross-Validation Rule

[malnahar]

Hello Friends,


Is there any way to create a cross-validation rule between 2 cells?
For example,

Cell A5 will have one of the following values (AA, BB, or AB)
In column K I have values like 111
222
333
444
Now, I need a code that will allow me to use 111 in B5 if A5 has AA, so if I mistakenly typed 222 In B5 the code will show me a message says that this 222 cannot be used with AA?
Please find attached for your easy reference.

Thanks in advance

[ChemistB]

Can each 2 letter code have multiple possible numeric codes or is there a specific Department for each Center? In the first case, a simple VLOOKUP will work. In the second, first you would need to set up those lists showing which Center has which possible departments.

[Rahul Nagar]

Hello ChemistB,

As per your attachment , please use following formula in B coloum

=VLOOKUP(A5,$J2:$K17,2,0)

this might be helpful

[malnahar]

Hi,

For every center there are several specific departments, ie, departments (444,555 and 666) are always to be attached to center AA.

I have updated the file, would you please have a look

Thanks

[shg]

See attached.

[malnahar]

Thank you shg,

Actually I am not worried about the values in column C, rather I am looking to have a formula in column D which says:
If A4=”AA” and “B4=”444” or “555” or “666” then return (Valid), otherwise (Invalid). And if A4=”BB” and B4=”111” or”222” or “333” then return (Valid), otherwise (Invalid) etc....

I am sorry for not being clear in my previous posts

Thanks in advance

[ikuogs]

This is what I've done. See attachment.

[ikuogs]

shg why are you a naughty moderator? (Just curious, not trying to embarrass you!)

[shg]

The admin thinks I have a warped sense of humor ...

[ikuogs]

Oh... btw I like your solution. Mine works but looks crude. Yours is much more elegant. malnahar please ignore my solution.

[malnahar]

Thanks a million Shg & ikugos,

Both are brilliant, Can I not ignore ikugos’ solution
I will need both solutions

Best regards and thanks again