Thanks to "Can Google Spreadsheets conditionally format based on a previous cells content?", I understand how to apply conditional formatting to a preceding cell, if I'm using a simple comparison, but what about something more complicated.

For example I have data in a column in the following format:

B4                 | C4
123 / clientname1  | 143 / clientnamea
345 / clientname2  | 143 / clientnameb

With a one off formula, below, I can split and compare the two numbers:

=(TRIM(LEFT($C4,(SEARCH("/", $C4))-1)))-(TRIM(LEFT($B4,(SEARCH("/", $B4))-1))) > 0

However, how would I create a reusable rule that uses something along the lines of 'current cell - minus - value one cell left', rather than hardcoding $C4, $B4 etc each time?


The answer to the question you linked contains the answer to this one. But since it apparently didn't get through, here it is again:

In conditional formatting, you enter the formula as it should apply to the upper left corner of the formatted range. The spreadsheet will apply it to the rest of the range using relative references. So, if the upper left corner of formatted range is C4, then

  • =B4 means "the value in the cell to the left of this one".
  • =$B4 means "the value of the cell in B column of this row"
  • =C$1 means "the value at the top of this column"
  • =$B$2 means "the value in cell B2"
  • Thanks @NormalHuman But how would I add that to the the formula I quoted? 'C4' seems to create an absolute comparison to C4 from all columns. '=C4' produces an error. – Jon Hadley Oct 15 '15 at 9:11
  • What is the range to which you are applying this formatting? (It's near the top of the conditional formatting dialog) – user79865 Oct 15 '15 at 13:05
  • C3:Z3 (There's nothing in Z3, but that's the closest I can get to 'all of this row, apart from the title column - which is a another story) – Jon Hadley Oct 15 '15 at 16:23
  • 1
    Then the formula would be =(TRIM(LEFT(C3,(SEARCH("/", C3))-1)))-(TRIM(LEFT(B3,(SEARCH("/", B3))-1))) > 0 because this is what you want for the cell C3 (upper left corner), and when applied, say, to F3, the references are remapped to =(TRIM(LEFT(F3,(SEARCH("/", F3))-1)))-(TRIM(LEFT(E3,(SEARCH("/", E3))-1))) > 0 – user79865 Oct 15 '15 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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