Okay... the only way I could get it to work in the same cell with the value
was to do them individually, because Excel would not allow relative
references in the conditional format using an icon set. It was way easier to
add the arrows to a separate cell. Here's the hard way:
With B2 selected (where I placed the 12 under August), select conditional
formatting , icon sets, and then 3 arrows.
Then click conditional formatting and manage rules...
In the manage rules dialog box, click Edit rule... and set up as follows:
(first row) when value is =$A$2 Formula
(second) when value is = =$A$2 Formula
then click ok and ok.
Double click on the format painter button and then click cell C2, D2, etc
one at a time (do not drag to select range). Click the format painter again
to turn it off when you're finished.
Since they will now each be comparing themselves to A2, you will need to
select each cell individually and edit the rule to refer to the cell
immediately to the left.
This will give you arrows in the cells the way you want them, but is a pain
to set up.
Easier way: place arrows immediately above or below your data.
For example, in cell B3, enter the following formula:
=if(B2A2,3,if(B2=A2,2,1))
Copy that across below each additional month.
Select the range that includes all of the formulas on row three for all
months and click conditional formatting, icon sets, and 3 arrows.
Then click conditional formatting, manage rules, edit rules, show Icon only,
and ok and ok to exit dialog boxes.
You can center or right align the arrows to place them in the cell as desired.
Hope this helps, good luck!
"JOHN HARRIS" wrote:
I have a spreadsheet that tracks the number of screens completed in doctors
offices each month. What I would like to do is set the CF in Excel07 so that
an arrow appears next to the number showing whether the screens for that
month increased, stayed the same, or decreased.
Col A Col B Col C
July August September
12 12 24
Nothing would be in Column A since it is the start point, Col B would have a
flat arrow, and Col C would show an UP arrow.
Cannot figure out how to do this, and would appreciate any help.
TIA
--
JOHN C. HARRIS, MPA, MBA
JCZ CONSULTING OF TAMPA BAY
813-361-8826