Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Conditional Formatting With Icon Sets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Conditional Formatting With Icon Sets
Post your code or formula. It should be a simple fix.
"mattagc" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Conditional Formatting With Icon Sets
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Conditional Formatting With Icon Sets
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. "joel" wrote: Post your code or formula. It should be a simple fix. "mattagc" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Conditional Formatting With Icon Sets
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Conditional Formatting With Icon Sets
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. "joel" wrote: 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. "joel" wrote: Post your code or formula. It should be a simple fix. "mattagc" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Conditional Formatting With Icon Sets
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" wrote: 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. "joel" wrote: 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. "joel" wrote: Post your code or formula. It should be a simple fix. "mattagc" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Conditional Formatting With Icon Sets
Im using formula is with this formula
=AND(A3<=M3,M3<=B3) "mattagc" wrote: 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" wrote: 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. "joel" wrote: 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. "joel" wrote: Post your code or formula. It should be a simple fix. "mattagc" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Conditional Formatting With Icon Sets
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(L1M1,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 "mattagc" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting (Icon sets - 3 Arrows) | Excel Worksheet Functions | |||
Conditional Formatting with Icon sets | Excel Discussion (Misc queries) | |||
Icon sets - conditional formatting - in Excel 2003 | Excel Worksheet Functions | |||
Conditional Formatting and arrow icon sets | Excel Discussion (Misc queries) | |||
Icon Sets in Conditional Formatting | Excel Programming |