Conditional formatting formula

[kprabhupaul]

I have two tables.

In Both tables I have data of NAMES and MAIL.

Now I want conditional formatting formula for Name column in table 2

Rule/Condition : If a name is entered in table 2 column which is out of the names that contain table 1

simply, if the entered name is not in table 1 names it shall be formatted.

find the attachment ..

Thanks in advance ... .. .

[etaf]

use a formula
=COUNTIF($F$8:$F$16,$M8)=0

for 2007, 2010 or 2013 excel version
Conditional Formatting

Highlight applicable range >>
$M$8:$M$16

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:

=COUNTIF($F$8:$F$16,$M8)=0

Format… [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

[kprabhupaul]

First of all thanks for the reply,

but I want to use table references instead of individual cell references in the formula ..

because my data will go broad day by day

I tried

Please Login or Register  to view this content.

But it's not working ..

Give that way too ..

[etaf]

this should work
tables references do not work directly in conditional formatting
but you can use indirect
=COUNTIF(INDIRECT("TABLE1[NAME]"),INDIRECT("Table2[@NAME]"))=0

see
http://www.get-digital-help.com/2012...ting-formulas/

[kprabhupaul]

Ok Brother,

it's working ..

the " Applies to " range is getting updated to the new range automatically when we are entering to the new row ..

[avk]

First of all thanks for the reply,

but I want to use table references instead of individual cell references in the formula ..

because my data will go broad day by day

I tried

Please Login or Register  to view this content.

But it's not working ..

Give that way too ..

Its tested in "Show formatting Rules for : "This Table"
If you add in both table highlighted cell if not match.

[etaf]

the " Applies to " range is getting updated to the new range automatically when we are entering to the new row ..

yes, thats the advantage of tables , they should auto update
also applies to pivot table ranges as well

however, i have shown a way to use table reference in conditional formatting
http://www.get-digital-help.com/2012...ting-formulas/

[kprabhupaul]

First method is also working brother ..

both " Applies to " range and " formula" getting updated automatically when going to new rows ..

thanks again ..

Good Day ahead ..

[etaf]

your welcome
if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here

[kprabhupaul]

Not working in my Real Document,

I'm using references of table in another sheet

but It's not allowing to use references of another sheet.

please find the attachment and solve ..

Names of tables in new file table1 = Parties and table2 = master

[kprabhupaul]

Here PARTY column of master table is the range to be formatted ..

[etaf]

try using
=COUNTIF('DASH H'!$B$13:$B$33,$C3)=0
as the conditional format
it should expand with the table
also only 1 entry that does not match - last one on row 112

i'm not sure about indirect and tables across sheets

[kprabhupaul]

Thanks brother, working

I solved by this way too ..

=COUNTIF(INDIRECT("PARTIES[PARTY]"),$C3)=0

[kprabhupaul]

A big thanks ..

Keep it up ..