Update Color of Bar/Pie chart based on value

[mrdugan]

Hello,
I have a problem to solve. I have a bar chart and pie chart that depict values based on a person's org. The bar chart shows activity of a person and the color of the bar indicates their org. The pie chart depicts the number of people in each org shown in the bar chart. Data:

P=Person

Person Activity Org Color Wanted
P1 120 A1 RED
P2 118 B2 YELLOW
P3 95 C3 ORANGE
P4 77 A1
P5 68 A1
P6 55 D4 BLUE
P7 48 B2
P8 32 D4
P9 25 A1
P10 17 C3

The pie chart would then show the same org/color info as
ORG Color Value
A1 RED 4
B2 YELLOW 2
C3 ORANGE 2
D4 BLUE 2

As I enter in data for the bar chart I want the color to automatically show in the bar as well as the pie chart. I also want the option to be able to add more orgs.

Thanks!

Mike

 

[Steve=True]

In order to change the colors without VBA, you will need to calculate which items should be for instance RED and then have the formula put in NA() if not RED.

You repeat the series for each color and chart the whole lot. Excel ignores the NA()s.

Then when you chart the lot, you will select the SHOULD be RED bar/column and change the color to red. Repeat for other colors.

Your data would look like this:

A B
R1 120 #NA
R2 #NA 118

Here are examples: http://peltiertech.com/Excel/Charts/ConditionalChart1.html