Conditional Formatting highlight cell in range based on a separate range

[charliew]

Afternoon everyone...

I am having a right mare with my conditional formatting formula and i feel like I've tried every variation and I've given up now...

I have a list of master codes in one sheet "EWC List" I then input incoming loads in another sheet "Stock" if while i'm inputting the EWC code i make a typo i want it to highlight.

i'm using:

=AND(NOT(ISBLANK(O2)),ISNA(MATCH(O2,'Full EWC list & chapters'!A2:A839,0)))

I have set it to apply to the whole of column O but it will still only work on cell O2...I know i'm doing something wrong but i cant figure it out...

i've also tried

=AND(NOT(ISBLANK(O:O)),ISNA(MATCH(O2,'Full EWC list & chapters'!A2:A839,0)))

and

=AND(NOT(ISBLANK(O2)),ISNA(MATCH(O:O,'Full EWC list & chapters'!A2:A839,0)))

and

=AND(NOT(ISBLANK(O:O)),ISNA(MATCH(O:O,'Full EWC list & chapters'!A2:A839,0)))

Please help me

Thank you so much in advance!!

 

[JamesCanale]

I think this might be it...
If the first cell in the formatting range is O2 - then I think this will work. You need $ in the match range, or that will increment too.

=AND(NOT(ISBLANK(O2)),ISNA(MATCH(O2,'Full EWC list & chapters'!$A$2:$A$839,0)))

 

[felixstraube]

You could also check correct data entry with data validation (Data/Data Validation):

Lets say you have this list of Master codes in EWC list:

Book1
	A
1	Master codes
2	ABC
3	ABD
4	ABE
5	ABF
EWC List

And name the range MasterCodes.

Then you add data validation like this in the cell where you are going to enter the codes (create one and copy down, or if it is formatted as a table it will apply to the whole column):

You will get the desired data validation message, and you also have a dropdown list with values to choose from:

If you think this works for you and need help setting this up, let us know.

 

[felixstraube]

Or you could try this formula: in your conditional formatting:

=(O2<>"")*NOT(ISNUMBER(MATCH(O2, 'EWC List'!$A$2:$A$839,0)))

And make sure that O2 is the same as the start of the range you applying the formatting to.

 

[charliew]

Thank you so much for your time and help! i'm in front of my spreadsheet now, so i will try your suggestions.

thanks again!

 

[charliew]

Wonderful thank you all!

Is there a way of highlighting if a format is incorrect?

For example the format NEEDS to be

XXXXXX/XXXXX

Can i get it to highlight if there are insufficient amount of characters either side of the /

thank you again!

 

[felixstraube]

Lets assume that your range starts in A2, try this:

=LET(t,A2,(IFERROR(ISNUMBER(FIND("/",t))*(LEN(TEXTBEFORE(t,"/"))=6)*(LEN(TEXTAFTER(t,"/"))=5),0)=0)*(t<>""))

In your conditional formatting