Copying Conditional Formatting With Icon Sets

[mattagc]

Hello,

I've been having "issues" with Excel 2007 and conditional formatting.

I've been trying to get an icon set (arrows) working with my data for awhile
now, and after several hours of searching the net, I'm still at a loss.

I have 180 groups of data that this needs to be applied too, so I don't want
to have to do it one cell at a time.

Here's what I'm trying to do:

When the cell L1 is greater than M1, display a red arrow, if they're the
same, yellow arrow, and if it's lower, green arrow. I have this working. The
issue comes into play when I try and copy it through cells L2 through
L181...it all references back to cell M1 because of the required absolutes.

Is there an easy workaround to this, or am I stuck creating a rule for all
180 cells?

Thanks in advance,

Matt

 

[joel]

Post your code or formula. It should be a simple fix.

 

[mattagc]

Well, I was going to take a screenshot to just show you, but I guess since I
cannot upload it here, I'll try and explain it to you. You'll have to bear
with me, I'm not an Excel-savant, much of what I know how to do with the
program is trail'n'error and finding instructions on the web...

Here's how I set up the format I have working...

1/ Click the cell (L3), click on Conditional Formatting, and click New Rule.
2/ Rule Type: Format all cells based on their value.
3/ Format Style: Icon Sets (3 Arrows (Colored), Reverse Order Icon)
4/ ...when value is ">" "=$M$3" "Number"
5/ ...when <= Formula and ">=" "=$M$3" "Number"

And then I hit okay, and it works. The value in M3 is a number calculated by
a formula within a cell.

Thanks,

Matt

 

[joel]

Remove the $

from
"=$M$3"

to

"=$M3"

the dollar sign is fixing the row at 3 and not allowing the fomula to change
as you copy the formula down the row.

 

[mattagc]

I've tried that before, and it doesn't work. It gives me an error about how
you cannot use relative references with a conditional formatting icon sets,
color bars...(I don't remember the exact wording as I'm away from my computer
at the time, and this computer does not have Excel (or my data) on it.)

Thanks,

Matt

 

[joel]

I have never had problems with the formula I gave you. I think relative
formating would be using R1C1 formating if you did RC-1.

 

[mattagc]

I just tried it again, and still a no-go. I removed the second $ from the
cell reference, but as soon as I hit okay, I get the following error message:

"You cannot use relative references in Conditional Formatting criteria for
color scales, data bars, and icon sets."

Thanks,

Matt

 

[joel]

Im using formula is with this formula

=AND(A3<=M3,M3<=B3)

 

[Peter T]

I don't think it's possible to use the equivalent of Formula-Is with
icon-sets. Only way I can think of is with a helper column, say to the left
of col-L

=IF(L1>M1,1,IF(L1=M1,0,-1))

Copy the formula down and apply the icon-sets

You could make the font white to hide those 1's & 0's, add a white border to
the right edge of the column, and resize the columns to make the pair look
like a single column. If you are already using col-K you might need to
insert the new column shifting all others right.

You might want to ensure the user understands the icons refer to cell L vs.
M, and not L vs. other L-cells as is what icon-sets refer to by default.

Regards,
Peter T