SOLVED

Conditional Formatting values across tables

Copper Contributor

Hi, 

Could someone please assist me with this. I have attached a photo to add context.

When I write values into the green table I would like the figures to become red if they are less then the corresponding cell in the yellow table. I know how to format each cells independently but is there a way to do it multiple times? It won't work if highlight multiple cells.

 

Cheers, 

Chris

 

Screen Shot 2018-11-25 at 8.21.13 pm.png

 

 

 

5 Replies

Hi,

you can do it column by column but you will still need to do the first cell manually 

 

- use conditional formatting for the first cell  then  use Format Painter and extend to the end of the column.

 

unfortunately: the empty cells will considered have the value ( 0) so they will be painted in RED till you fill them with correct numbersCapture.PNG

best response confirmed by ChrisJP (Copper Contributor)
Solution

I'd suggest the rule with formula

=(E3<A3)*NOT(ISBLANK(E3))

applied to entire range. With that you don't format blank cells and do not generate a bunch of rules (one rule for each cell) as with Format painter.

 

Works Perfectly,

 

THANKYOU!

Hi,
what would the 2 formulas be, if the value was < A3 but only within 10% to equal yellow

and if it was greater then <10% = red

Hi,

 

When yellow

=(E3<A3)*(E3>=0.9*A3)

and red

=(E3<0.9*A3)*NOT(ISBLANK(E3))

if I understood you correctly

 

1 best response

Accepted Solutions
best response confirmed by ChrisJP (Copper Contributor)
Solution

I'd suggest the rule with formula

=(E3<A3)*NOT(ISBLANK(E3))

applied to entire range. With that you don't format blank cells and do not generate a bunch of rules (one rule for each cell) as with Format painter.

 

View solution in original post