5

I have a whole bunch of cells (28 so far) that need conditional formatting.

Here is what the data looks like:

enter image description here

I want the colours in the top "Last Period" metric cells to vary depending on the values inside the three rows below: so if the top number is bigger than the grey number, it's light green; if also bigger than the blue number, the text goes yellow; and if also bigger than the purple number, the cell shoots confetti out of the screen and onto the user's lap. Likewise if it is lower it sends a negative signal(s), perhaps ultimately producing a foul sour milk smell, filling the user's office with shame.

Here is an example of one formula, which turns the cell background a nice shade of green when the percent difference between D5 and D6 is higher than 1.

=D5/REGEXEXTRACT(D6,"\((.*)\)")*1>1

It works—BUT (there's the "but"), I can't apply a proper cell range to this condition, because I really want to say, "if the cell being tested is bigger than the cell below it, turn green." Otherwise it's always testing against D5 and D6.

From what I understand, the only way I could do it is to create conditional formatting for each and every of the dozens of cells... which is just not going to happen.

Sheet link

Would a script be better for this kind of feat?

1 Answer 1

4

Try this:

=INDIRECT(ADDRESS(ROW(),COLUMN(),4))/REGEXEXTRACT(INDIRECT(ADDRESS(ROW()+1,COLUMN(),4)),"\((.*)\)")*1>1

INDIRECT
ADDRESS 1 ROW
COLUMN

1 The third parameter in ADDRESS() is absolute_relative_mode

[ OPTIONAL - 1 by default ] - An indicator of whether the reference is row/column absolute. 1 is row and column absolute (e.g. $A$1), 2 is row absolute and column relative (e.g. A$1), 3 is row relative and column absolute (e.g. $A1), and 4 is row and column relative (e.g. A1).

2
  • 1
    Thanks ale! What's the "4" in COLUMN(),4 mean?
    – Drewdavid
    Apr 20, 2017 at 22:16
  • I've updated my answer.
    – ale
    Apr 21, 2017 at 12:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.