I have a whole bunch of cells (28 so far) that need conditional formatting.Here is what the data looks like: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>1It worksBUT (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 linkWould a script be better for this kind of feat?
How to apply a relative reference in conditional formatting formula?
google spreadsheets;conditional formatting
Try this:=INDIRECT(ADDRESS(ROW(),COLUMN(),4))/REGEXEXTRACT(INDIRECT(ADDRESS(ROW()+1,COLUMN(),4)),\((.*)\))*1>1INDIRECTADDRESS 1ROWCOLUMN 1 The third parameter in ADDRESS() is absolute_relative_mode[OPTIONAL -1by default]- An indicator of whether the reference is row/column absolute.1is row and column absolute (e.g. $A$1),2is row absolute and column relative (e.g. A$1),3is row relative and column absolute (e.g. $A1), and4is row and column relative (e.g. A1).