conditional formatting across worksheets

[jcavigli]

Hello,

I am having a problem. So, I will post a somewhat trivial example - but it would be of great use to me.

I have a workbook with two worksheets. The first worksheet is called 'Enter Data Here' and a person can write in OR paste a list of names in column A. I would like to conditionally format this column of data.

1- If the name written in OR pasted in is apart of the 'Accepted Names' column on the 2nd worksheet then the color should be GREEN.
2- If the name written in OR pasted in is NOT apart of the 'Accepted Names' column on the 2nd worksheet then the color should be RED.

Attached is a sample. I think it makes what I am trying to say much easier to understand.

Thank you in advance for any help.

[martindwilson]

define a name for your accepted names range say "nameslist"(use insert/name/define/refers to
='Accepted Names'!$A$2:$A$9)
then cf condition 1 formula is
=MATCH(A2,nameslist,0)
green
condition 2
=ISNA(MATCH(A2,nameslist,0))
red
warning i see from your example that some names arnt the same length
so use trim
=MATCH(TRIM(A3),TRIM(nameslist),0)

[jcavigli]

I will definitely have to use that trim function, the names will not have the same string length. Let me try this stuff out and get back to you.

[jcavigli]

I used formulas 1 and 2 on the 'Enter Data Here' worksheet. I am applying them to the range A2:A1000. (I would like to apply it to the whole column later on)

For some reason some names come back black? I must be doing something wrong. I have attached my sample with your formulas.

[jcavigli]

ok - i got it



Thank you for your help.