Conditional formatting look up into a table/named range

[katel99]

Hello

Please could someone help me write a formula, that looks up between workbook1 and named ranges/tables on workbook2.

In book1 I have a "Role" column (D) and then a "Name" column (E).

In book2 I have named ranges (also made into tables) that are headed with a variety of roles. One of which is "Leavers".

In book 1, I want to highlight the row in a colour, if the name in column E appears in the "Leavers" array in book2.

I have tried a variety of things, i.e. Match, VLookUp, but I think I must be mistyping somewhere...

Thanks in advance,

Kate

 

[steve the fish]

I dont think you can do that using two different workbooks. Can you copy the named range over to the workbook1?

 

[katel99]

Can I have it on a separate sheet on the same workbook? In which case, yes I can

So assuming it's Sheet1 and Sheet2 in the same workbook, is this possible?

 

[Fluff]

Select the range you want this to work on then
Home tab > Conditional Formatting > New Rule > use a formula > =MATCH($E2,Leavers,0) > select format > Ok
Where $E2 must refer to the first row in the selected range

 

[katel99]

Hiya,

Thanks for your reply.

I've got this working now!

Any ideas on how I can make it format the whole row, rather than the one cell with a name in it?

Thanks

 

[steve the fish]

What is the exact formula you are using for the conditional formatting? What is the top left cell of the range you need to format?

 

[katel99]

Hiya,

I edited my post, so not sure if your reply is in relation to my earlier one. If so, apologies, I was having a dumb moment and I now have the formula working to highlight the correct cells

The formula I am using is;

=MATCH($E2,Leavers,0)

What I would like to do now is (if possible) highlight the whole row, where a Leavers name appears in column E.

Thank you all very much for your help - conditional formatting is not my strong point

 

[steve the fish]

You set the applied to part as:

=$2:$10

Change the 10 to whatever is appropriate for you.

 

[katel99]

Amazing, thank you all!!