• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 695
  • Last Modified:

3 Arrows (Colored) Conditional Formatting

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
jppinto
Asked:
jppinto
2 Solutions
 
tdlewisCommented:
Would cell shading and text color work as well? See the attached spreadsheet.
Conditional-Arrows.xlsx
0
 
redmondbCommented:
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
 
mark_harris231Commented:
@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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mark_harris231Commented:
Gah - Brian beat me to the same conclusion.  ;)
0
 
redmondbCommented:
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
 
jppintoAuthor Commented:
Both solution serve as a walkaround for this Excel limitation. Thanks for your participation.

Regards,

jppinto
0
 
mark_harris231Commented:
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
 
redmondbCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now