(I swear, it’s easier and quicker to post a question here than to try to find it in the Excel online help).
Here’s my problem:
I need to create a spreadsheet that displays a graphic representation of whether or not a number in each row is “good” or “bad”. In some cases (sales growth, eg) positive is good; in some cases (fraud) positive is bad.
The direction of the arrow is straight-forward: positive = up, negative = down.
My attempted solution(s):
Plan A:
Row 1:
B1 has the value I’m testing; in this row, up is good, down is bad. C1, D1, E1 and F1 have 4 arrows (which I got by inserting symbols) up red, up green, down red, down green.
In cell A1 I have: =IF (B1>0,D1,E1).
Simple and elegant…except that the colors I put on the arrows don’t translate into the cell reference in A1 – so the arrow is pointing the right way, but black.
So, Plan B: only need 2 arrows (up and down), and apply the proper coloring through conditional formatting. I thought I knew how to use conditional formatting in Excel 2003…in 2007 they’ve made it so incredibly complicated that I can not make heads or tails of the online help.
(It appears that they’ve included green and red arrows as icons you can apply – except that you can only have green up and red down arrows).
I would prefer to get Plan A to work – is there a way that a cell reference can also include the format (in this case, color) of the referenced cell?
If I have to go to conditional formatting, what do I need to do?
thanks!