Conditional Formatting Help- Condition based on value in separate table

[salmonchild]

Afternoon followers of Chandooism,

I'm stumped again on solving a problem, even 6 cups of tea in a two hour period has not helped me.

I have a table of data that I would like to apply conditional formatting to. I would like, when people enter a location (4 digit code), for the whole row to be formatted (red text/background, whatever) if there is any entry on a separate table with the same location code.

i.e.

Location is entered to sheet 1 E2
Excel then checks sheet 2 A:A and if there is one or more match in any of the rows, applies the conditional formatting.

Is this possible? I have been tooling around trying multiple IF functions but am getting nowhere.

Any assistance would be gratefully received.

Oli

 

[SirJB7]

Hi, salmonchild!
Apply this CF formula to the table (including row 1):
=SI.ERROR(COINCIDIR($A1;Sheeet2!$A:$A;0);0)>0 -----> in english:=IFERROR(MATCH($A1,Sheeet2!$A:$A,0),0)>0
Regards!

EDITED

PS: Where it says $A1 it should say $E1.

 

[NARAYANK991]

Hi ,

The problem is you cannot use references to other worksheets in CF formulae ; the solution is to use named ranges.

Create a named range , say Sheet2_ColA , and in the Refers To box , put in =Sheet2!$A:$A

Now , on sheet1 , with the cursor in E2 , enter the following CF formula :

=MATCH($E2,Sheet2_ColA,0)>0

and select a fill colour of your choice.

If E2 contains data for which there exists a match somewhere in column A in Sheet2 , the cell E2 will be coloured.

Narayan

 

[salmonchild]

Chaps- thank you very much.

Since posting I put in a workaround by having a helper column

=IF(COUNTIF(Talent!$A:$A,'Retail Master'!$E2)>1,"Y","N") and then based the CF on that. I am running it down 6000 rows so I don't think it will slow the calculation time of the sheet by too much.

I am going to step away from it now as clearly my brain power is waning, then have a look at your solutions tomorrow.

Cheers

Oli

 

[SirJB7]

Hi, salmonchild!
To speed up a bit (but I don't think that much more if just for 6000 rows), adjust column A range from $A:$A to $A1:$A6000. Or define a dynamic named range.
Regards!

 

[salmonchild]

Mods/Ninjas is there a way to alter the thread title with [SOLVED]?

 

[SirJB7]

Hi, salmonchild!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
PS: Manual old edition not in use any more, new automatic feature not available yet.