lionelhill
Technical User
I would like to use traffic-light icons to compare data in columns B, C etc. with the corresponding data in the same row in column A. I.e. cell C56 should have a red traffic light icon if it contains a value less than half that in A56, green traffic light if it is at least equal to A56, and otherwise it should contain a yellow traffic light icon.
I expected I could do this by entering traffic light icons in the conditional formatting, then going to "manage rules", "edit rule", and switching the rule to type "number", and entering appropriate formulae in "value". I can enter a formula to compare all my data to $A$1, but if I attempt to use a value $A1, i.e. relative addressing, Excel2007 tells me this is specifically forbidden in conjunction with icons etc.
I tried to cheat by entering the formula
=index(colAdata, row())
in one value box, and the same thing divided by two for the other value box. colAdata is a named range corresponding to the whole of column A.
This gives me results that are very hard to predict. Sometimes changing data elsewhere on the spreadsheet, in a cell with no connection whatsoever to my named numerical range or any cell in the formatted range, causes all the formatting to change. Changing a number in the range and then changing it back to its original value can lead to a change in the pattern of traffic light icons. The icons don't always seem to have the colour I would expect.
I would be very grateful if anyone could point me at a good source of information about how to use Excel2007's new conditional formatting features.
I'd also be grateful if anyone has found a way to do what I'm trying to do; is there a way to persuade Excel to use relative addressing in conjunction with icons and other new conditional formatting features?
Sorry that this post is very similar to another recent post on how to do something similar in Excel2003; I agreed there to repost as I'd deviated into a different version...
Thanks!
I expected I could do this by entering traffic light icons in the conditional formatting, then going to "manage rules", "edit rule", and switching the rule to type "number", and entering appropriate formulae in "value". I can enter a formula to compare all my data to $A$1, but if I attempt to use a value $A1, i.e. relative addressing, Excel2007 tells me this is specifically forbidden in conjunction with icons etc.
I tried to cheat by entering the formula
=index(colAdata, row())
in one value box, and the same thing divided by two for the other value box. colAdata is a named range corresponding to the whole of column A.
This gives me results that are very hard to predict. Sometimes changing data elsewhere on the spreadsheet, in a cell with no connection whatsoever to my named numerical range or any cell in the formatted range, causes all the formatting to change. Changing a number in the range and then changing it back to its original value can lead to a change in the pattern of traffic light icons. The icons don't always seem to have the colour I would expect.
I would be very grateful if anyone could point me at a good source of information about how to use Excel2007's new conditional formatting features.
I'd also be grateful if anyone has found a way to do what I'm trying to do; is there a way to persuade Excel to use relative addressing in conjunction with icons and other new conditional formatting features?
Sorry that this post is very similar to another recent post on how to do something similar in Excel2003; I agreed there to repost as I'd deviated into a different version...
Thanks!