# Conditional format formula to select a previous cells content (current cell -1)

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?

• `=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"
• 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