Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

up vote 2 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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