Conditional formatting based on formatting in other table

[TheMonk]

Say,I have 2 tables...Tables are related...

1 3 5 7
8 8 9 10
11 12 13 14

&
2 3 5 5
1 1 1 1
3 4 4 4

I have highlighted top 5 values in first table using conditional formatting rules.Now I want to highlight cells in the second table based on first table...Assume 11 12 13 14 7 10 are highlighted in first table....I want to highlight cells in second table occupied same position as of in highlighted cells in first table..i.e. 3 4 4 4 5 1 should be highlighted...How can I do that?

 

[pjmorris]

Hi,

You need to use Conditional Formating, but choose the 'Use a formula to determine which cells to format' and then apply the criteria which gives the same result as in the first table. For the situation you've described above you'd need to use something like =large($A$1:$D$3,1)=A1 then repeat for the top 5 by additional CF:

=large($A$1:$D$3,2)=A1
=large($A$1:$D$3,3)=A1
=large($A$1:$D$3,4)=A1
=large($A$1:$D$3,5)=A1

Hope this helps.

 

[TheMonk]

What if I have 100 columns & 5 rows..I have selected top 200 items in first table...And I want to highlight 200 cells in second table having same position.

 

[pjmorris]

This formula might work:

=large($A$1:$CV$5,200)<=A20

To use this highlight the second table, (I've assumed the top left cell is A20, which would make the bottom right CV24) and then apply the conditional formating using the 'use a formula to determine which cell to format' option and then use the formula above.

HTH.

 

[TheMonk]

This formula might work:

=large($A$1:$CV$5,200)<=A20

To use this highlight the second table, (I've assumed the top left cell is A20, which would make the bottom right CV24) and then apply the conditional formating using the 'use a formula to determine which cell to format' option and then use the formula above.

HTH.

No,it is highlighting all the cells in second table...I think '<=A20' part is not correct..

 

[pjmorris]

Hi TheMonk,

I've just checked the formula.

As written it ought not to be highlighting all the cells in the second table - I suspect that you've not got exactly the same formul as shown above. If you have something like: =large($A$1:$CV$5,200)<=$A$20 then the formula actually only tests the value in one cell and hence they'll all respond the same way.

Secondly, the formula is wrong! So, to hopefully get this working:

1. Highlight all your second table, from cell A20 to cell CV24.
2. From Conditional Formating select New Rule, 'Use a Formula to determine which cells to format', then in the box ' Format values where this formula is true' enter this: =large($A$1:$CV$5,200)<=A13. Do remember to ensure the '$' signs are in the places shown.

Hope this works.

Regards