Tips on conditional formatting.
I've been toying around with conditional formatting for a little while now and I'm simply stumped for some reason. I want certain cells to show a graphic depending on what the cell value is at any given time.
This is what I want:
It looks like it should be easy to set up,but I've been hitting my head against the wall for about an hour now with no results, so any help would be appreciated. Or is what I want not possible?
Thank you.
This is what I want:
I want to have a checkmark when a cell is between X and Y.
I want the same cell to have an arrow pointing up when the value in cell is over Y and an arrow pointing down when it is under X.
It looks like it should be easy to set up,but I've been hitting my head against the wall for about an hour now with no results, so any help would be appreciated. Or is what I want not possible?
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The only problem with it is when the number is 7 it will show the up arrow and when it is 4 it will show the check. I believe if it is either X or Y exactly it should be checked as well. I have been unable for it to be no check when it is X or Y.
Cond-Format-Icons.xlsx
Cond-Format-Icons.xlsx
Sorry formatting was only set on Cell C2
peetjh,
That is a simple matter switching the operators in the drop downs to the left of the Value boxes.
Patrick
That is a simple matter switching the operators in the drop downs to the left of the Value boxes.
Patrick
You would have to explain how because the only options are >= and <= depending on which option you choose first. So in either case you can get it to be an up arrow or a check but unless I am just brain dead today which is possible at this time of day I don't see the any way to get no icons when value is = to X or Y.
OK fine, then make the value 7.000000001 for the up arrow, and all is good. Does not change the substance of my answer one iota.
ASKER
Patrick,
I'm using excel 2007 but I do not see any way to use custom icon sets like you have. Under format style there is no option to choose custom at all. Am I being thick or something?
I'm using excel 2007 but I do not see any way to use custom icon sets like you have. Under format style there is no option to choose custom at all. Am I being thick or something?
thirtywinter,
Instead of trying to select 'Custom' (which won't be there), start off with your icon set as green arrow up, right pointing yellow arrow, red arrow down. Then, edit the rule, and in the selector for the yellow arrow select instead the green check mark.
Patrick
Instead of trying to select 'Custom' (which won't be there), start off with your icon set as green arrow up, right pointing yellow arrow, red arrow down. Then, edit the rule, and in the selector for the yellow arrow select instead the green check mark.
Patrick
ASKER
Patrick,
I'm with you all the way to the yellow arrow. After that there is no way to select or change any of the arrows. Is it possible that's an Excel 2010 function?
I'm with you all the way to the yellow arrow. After that there is no way to select or change any of the arrows. Is it possible that's an Excel 2010 function?
Crap, that's a new Excel 2010 feature.
Starting up Excel 2007 to see what I can do as a workaround.
Starting up Excel 2007 to see what I can do as a workaround.
In Excel 2007, I think the best you are going to be able to do is Dave's suggestion in http:#a36331785
ASKER
Hmmm... not unexpected.
However, I feel that your solution was so blisteringly elegant that I just can't ignore it. I think I'll share the points among the two of you.
On a personal note, I just can't believe I couldn't get the conditional formatting to work the way I wanted for me without help. I'm not sure where my head was.. :D
However, I feel that your solution was so blisteringly elegant that I just can't ignore it. I think I'll share the points among the two of you.
On a personal note, I just can't believe I couldn't get the conditional formatting to work the way I wanted for me without help. I'm not sure where my head was.. :D
thirtywinter,
Glad to help. Mine does work in Excel 2010, but not in Excel 2007. Bummer, but then again that gives you another reason to upgrade :)
Seriously though, upgrading to Excel 2010 is a must if only so that you can then bask in the glory that is PowerPivot.
Patrick
Glad to help. Mine does work in Excel 2010, but not in Excel 2007. Bummer, but then again that gives you another reason to upgrade :)
Seriously though, upgrading to Excel 2010 is a must if only so that you can then bask in the glory that is PowerPivot.
Patrick
ASKER
It's on my to-do list actually.. :)
Dave