Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

formula

share|improve this question
up vote 3 down vote accepted

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:

You cannot use relative references in conditional formatting criteria for color scales, data bars, and icon sets.

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.

=IF(OFFSET(E10,0,-1)>500,1,IF(OFFSET(E10,0,-1)=500,0,-1))

alt text

alt text

The formula should be:

=OFFSET(E10,0,-1)>500

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:

Offset( range, rows, columns, height, width )
  • range is the starting range from which the offset will be applied.
  • rows is the number of rows to apply as the offset to the range. This can be a positive or negative number.
  • columns is the number of columns to apply as the offset to the range. This can be a positive or negative number.
  • height is the number of rows that you want the returned range to be.
  • width is the number of columns that you want the returned range to be.
share|improve this answer
    
Tried that and it's giving me a "Please enter a valid formula" error. – nmuntz Aug 5 '10 at 14:34
    
@nmuntz I have updated the answer. – Shaji Aug 5 '10 at 15:41
    
That worked out great ! Thank you very much! I would have never guessed that you cant use formulas with icon sets – nmuntz Aug 5 '10 at 16:30

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:

=INDIRECT(ADDRESS(ROW()+X,COLUMN()-Y))>Z

Here, X is the vertical offset, Y is the horizontal offset and Z is the value against which you are testing.

In the OP's example, it would be:

=INDIRECT(ADDRESS(ROW(),COLUMN()-1))>500

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.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .