[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

3 Arrows (Colored) Conditional Formatting

Posted on 2012-09-11
8
Medium Priority
?
682 Views
Last Modified: 2012-09-11
Please take a look at the attached file.

What I want to do, is using the 3 Arrows conditional formatting to put an error on each cell indicating if the value is grater than the previous column (on the same row), should display a Green arrow, is eqqual, should display a Yellow one or is less, should display an Red arrow.

Example:

B1 is greater than A1 - should have a Green arrow
B2 is greater than A2 - should have a Green arrow
B3 is greater than A3 - should have a Green arrow

C1 is greater than B1 - should have a Green arrow
C2 is eqqual to B2 - should have a Yellow arrow
C3 is less than B3 - should have a Red arrow

Hope this is clear.

Thanks,

jppinto
Conditional-Arrows.xlsx
0
Comment
Question by:jppinto
8 Comments
 
LVL 10

Expert Comment

by:tdlewis
ID: 38388450
Would cell shading and text color work as well? See the attached spreadsheet.
Conditional-Arrows.xlsx
0
 
LVL 26

Accepted Solution

by:
redmondb earned 1000 total points
ID: 38388530
Hi, jppinto.

Please see attached.

Excel doesn't allow relative references with "Arrows" (i.e. icon sets). I got around this by using a Name ("Joe") which refers to the previous cell on the same row.

However, to keep Excel happy, I've been forced to create a separate Conditional Format for each cell. If instead you try to merge them then the Conditional Formatting will fail.

I don't understand why this is so, therefore you need to be careful with this!

(BTW, I'm using Excel 2010.)

Regards,
Brian.Conditional-Arrows-V2.xlsx
0
 
LVL 10

Assisted Solution

by:mark_harris231
mark_harris231 earned 1000 total points
ID: 38388539
@jppinto - As you can't use relative cell references when using icon sets, the only way to do this using the built-in conditional formatting would be to define a rule for each cell:

For Cell B1, create Formatting Rule:
Green Arrow  when value is >=  '=$A$1'
Yellow Arrow when value is <= Formula and >= '=$A$1'
Red Arrow when < Formula

For Cell B2, modify as:
Green Arrow  when value is >=  '=$A$2'
Yellow Arrow when value is <= Formula and >= '=$A$2'
Red Arrow when < Formula

If you define all the cells in a column, you can use Format Painter to copy the Conditional Formatting to subsequent columns, but you will need to edit each one to increase the column number by "one"  (e.g., $A$1 changed to $B$1 for cell C1 formatting).  A bit of a pain, and maybe unworkable if you have a large number of rows/columns, but should be a one-time setup.

I played around with trying to "trick" the conditional formatting formula by using CELL("address",...) and INDIRECT(), but it saw through my attempts and wouldn't let me use a relative reference no matter how it was cloaked.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 10

Expert Comment

by:mark_harris231
ID: 38388543
Gah - Brian beat me to the same conclusion.  ;)
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38388688
Sorry, Mark!

With a lot of cells, I'd be more concerned about the load on Excel than the hassle of creating the formats - which could always be done with a macro.

[BTW, the manual method can be made quite a bit faster - instead of using "$A$1", use "A1" (or a Name). Then copy cell B1 and "Paste Formatting" to C1. Now select each of the remaining cells in turn clicking on the Redo button (or <CTRL-Y>) for each one.]

Cheers,
Brian.
0
 
LVL 33

Author Closing Comment

by:jppinto
ID: 38388706
Both solution serve as a walkaround for this Excel limitation. Thanks for your participation.

Regards,

jppinto
0
 
LVL 10

Expert Comment

by:mark_harris231
ID: 38388710
No apologies necessary.  Getting members answers as quickly as possible is what it's all about!

Clever use of the Named Ranges.  Pity Excel doesn't support relative references for the icon sets.  I can't think of any good reason why that should be.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38388754
Thanks, jppinto.

Mark,
Sympathy rather than an apology!
I suspect that MS were concerned about speed - I'd bet that the work-around would run out of steam a lot sooner than non-relative reference icon sets.

Edit: In my previous comment I mentioned a faster "manual" way. Of course, this only works when using a Name.

Regards,
Brian.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question