I need to create a conditional formatting rule that will apply some color if the cell to the left has a value greater than 500, however it is not accepting my formula:
=OFFSET(0,-1) > 500
Can anyone tell me what am I doing wrong?
Edit: I think you would not be able to use conditional formatting this way with icon sets. I got the following error when I tried:
I was, however, able to achieve the same by applying this formula to right column and then applying conditional formatting rule on this column as show in the screen shot.
The formula should be:
In Excel, the Offset function returns a reference to a range that is offset a number of rows and columns from another range or cell.
The syntax for the Offset function is:
There is a way of achieving the equivalent of Offset in Conditional formatting with relative references.
In the cell you want conditionally coloured, select the conditional formatting option Use a formula to determine which cells to format. In the formula bar use the following syntax:
In the OP's example, it would be:
You then just need to define the formatting; you can't have the traffic light symbol but you can set the cell's background or font to be a specific colour.