Conditional formatting (icons) applied to a range using formulas

Greetings - I'm using Excel 2011 for Mac.

Sample file attached.

Screen Shot 2017-02-22 at 8.53.04 PM.png

My watered down example consists of tracking "start" and "end" over a period of 7 days. So not including headers/labels there are 2 rows (B2:I3) and 7 columns. The data are all integers. I want to compare the value of B2 to B3 and display a "check" icon in the cell with the greater value and an "x" icon in the other. i'd also like to display the "!" icon if they happen to be equal. When I create a rule and apply it to B2 only, the "check" and 'x" icons display as expected.


My goal is to apply this rule to the entire range (B2:I2) and have the values being compared iterate to the end of the range. Unfortunately, when I apply it to the range, each successive value is compared to the first value tested ($B$3) and thus the icons do not display properly. I also don't see any way to specify that the "!" icon is to be displayed when the values are equal. Am I missing something?

Screen Shot 2017-02-22 at 9.06.18 PM.png

Screen Shot 2017-02-22 at 9.16.04 PM.png

I tried putting =B3 in the formula but get an error saying that relative references are not allowed. I know I can create a separate rule for each cell, but I find it very hard to believe that this would be the only way to go.

And finally, I find the 3 sections of the icon set rule dialog to be a little confusing. For the section relating to the "!" icon, it says

When value is < Formula and

Which formula is it referring to?


Similarly, the section for the "x" icon reads:

When value is < Formula

Which Formula is THIS one referring to?

Try to use defined name instead of cell reference.

to define start value: stay in B2, hit Ctrl-F3:

Name: Start
Refer to: B$2
(B$2 to refer to row2 everywhere you are)

Similar to define end value:
Name: End
Refer to: B$3

Set icon for value for cell B2:

First icon: when value is >=; Value:=end; type = Formula

Second icon: when < Formula (mean < end (cell B3)) and >; value: =0; type: Formula

Third icon: When <= Formula (mean <=0)

Hit OK

Apply to $B$2:$I$2

Similar process for B3.

Please ignore #2, it is not right solution.

Try another with INDIRECT to refer to cell, like this:

=INDIRECT("R2"&COLUMN(),0)

See attachment.

Hi,

As far as I can see, there is a separate rule for each cell in that workbook, in which case you do not need INDIRECT- you need only refer to the cell above/below directly with a fixed reference.