difficult conditional formatting

[ahhhmed]

This is a difficult task that I could not possibly solve.

In Sheet1 I have the following:

B2 has a name John

B3 has a name Peter

C2 has a number 5

C3 has a number 3


In Sheet 2 I have the following:

D12 has a name John

D18 has a name peter


What I want to do is the following:

I want to fill D12 and D18 in Sheet2 red when the number in C2 and/or C3 in sheet1 is 5


Your help is highly appreciated.

 

[Luke M]

On sheet1, define col C as "Values". Define col B as "Names".

On Sheet 2, CF formula for D12 is:

=INDEX(Values,MATCH(D12,Names,0))=5

Format red.

 

[ahhhmed]

Thanks Luke M.

A genuine answer!

 

[ahhhmed]

If I have a list in Sheet2, can I make the CF cells which are red to come at the end of the list?

 

[Luke M]

Maybe w/ a helper column...

Helper formula:

=5<>INDEX(Value,MATCH(D12,Names,0))

Sort the helper column ascending.