?
Solved

Excel Conditional Formatting Question

Posted on 2012-09-18
3
Medium Priority
?
185 Views
Last Modified: 2012-11-14
I want the Conditional Formatting Color Scale applied to the area of the attached chart H5:H57, but it won't allow me to as I have a formula that is returning NA()  The formula is returning NA() by design so that the graph will chart correctly. For some reason, I can't get both the conditional formatting color scale and the graph to chart correctly simultaneously.

Please see the attached, and thanks in advance for your help.
EE2.xlsm
0
Comment
Question by:Tim Jackoboice
  • 2
3 Comments
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 38412310
You could add another conditional formating rule with the formula =ISNA(G5) and set the colour appropiately

I have attached your example with this done and highlighted yellow

Michael
EE2.xlsm
0
 

Author Comment

by:Tim Jackoboice
ID: 38414179
@Michael74:

When I attempt to Conditionally Format with Color Scales cells G5:K57, it still doesn't work -- no formatting occurs. I do see where where you added the ISNA() rule to the Conditional Format rules, and it highlights those cells in yellow.

The objective of my Conditional Formatting is to show the relative comparision between the columns of years on the chart, using the Color Scales option. That option just doesn't work yet when applying it to cells G5:K57 yet, and is what I'm trying to accomplish.

Is there a rule I can manually add to the Conditonal Format rules that can replicate the Color Scales option, perhaps?

Thanks -- hope you know an easy workaround to this. I appreciate it.

Tim.
0
 
LVL 23

Accepted Solution

by:
Michael Fowler earned 1500 total points
ID: 38420558
The only think I can think would be to have a hidden sheet that contains the data to display  the chart. The hidden sheet would be linked to the data in the front sheet so it remains in sych but alter the formaul to display #N/A on the hidden table as required

Michael
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

864 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