conditional formattinggoogle sheets
I am having trouble figuring out how to get conditional formatting to work in my Google spreadsheet. I use the second sheet to import prices of projectors, but a few don't update.
I want the row of prices to turn green when there is the LEFT function in the cell such as =LEFT('Import Page'!B4,7), but when I try to get the conditional formatting to work using the "cell contains" "left" formatting it does not work.
Here is the image
Use ~ (tilde) to escape + (plus sign), = (equal sign) and also other special characters like * (asterisk) and ? (question mark) that are used as wildcards.
References
Apply conditional formatting rules - Docs editors Help
Try this:
=INDIRECT(ADDRESS(ROW(),COLUMN(),4))/REGEXEXTRACT(INDIRECT(ADDRESS(ROW()+1,COLUMN(),4)),"\((.*)\)")*1>1
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).
Best Answer
What you are attempting to do is impossible. Google Sheets functions reference only the computed value, not the underlying function text. Therefore, if your conditional formatting rule says that it should be green if it includes the text "left", it will read your cell, see that it contains only "$529.91," and conclude that the rule should not apply.