Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 21st 05, 05:37 PM
Booger_Boy
 
Posts: n/a
Default Colors of columns after sorting data in the supporting table

When creating a chart off of a table, the colors of each column appear to be
in the order of their display in the chart. For example, let's say I have
January data as red, February data as white and March data as blue. Then I
sort the table in the Excel sheet by value. Now the labels are ordered
February, March, January. Well, now the first column (February) is red, the
second (March) white and the third (January)blue.

Is there a method to hard-code the column color to the data label in the
charts rather than simply the order that the data appears?

  #2   Report Post  
Old January 22nd 05, 03:36 PM
Brian Reilly, MS MVP
 
Posts: n/a
Default

I'm about to do this on a project where I want consistent colors by
"Brand Name" and I plan to use a VBA routine and a Select Case
statement on the "Brand Name" text and then apply the correct color
index. You just have to iterate throught the datapoints.

Brian Reilly, PowerPoint MVP
  #3   Report Post  
Old January 22nd 05, 03:58 PM
Jon Peltier
 
Posts: n/a
Default

BB -

You might be able to make a conditional chart with your data:

http://peltiertech.com/Excel/Charts/...nalChart1.html

It will cost you a few extra columns and some formulas, but it is dynamic as the
data changes, whereas the VBA procedure would have to be rerun.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Booger_Boy wrote:

When creating a chart off of a table, the colors of each column appear to be
in the order of their display in the chart. For example, let's say I have
January data as red, February data as white and March data as blue. Then I
sort the table in the Excel sheet by value. Now the labels are ordered
February, March, January. Well, now the first column (February) is red, the
second (March) white and the third (January)blue.

Is there a method to hard-code the column color to the data label in the
charts rather than simply the order that the data appears?


  #4   Report Post  
Old January 22nd 05, 04:31 PM
Andy Pope
 
Posts: n/a
Default

Hi,

Depending on the complexity you may be able to use a conditional chart.
http://www.andypope.info/ngs/ng32.htm

Although the data layout technique will work in MSGraph it would not be
dynamic unless you used some VBA code.

Cheers
Andy

Brian Reilly, MS MVP wrote:
I'm about to do this on a project where I want consistent colors by
"Brand Name" and I plan to use a VBA routine and a Select Case
statement on the "Brand Name" text and then apply the correct color
index. You just have to iterate throught the datapoints.

Brian Reilly, PowerPoint MVP


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
  #5   Report Post  
Old January 22nd 05, 09:31 PM
[email protected]_reillyand.com
 
Posts: n/a
Default

Hi Andy,
Could do this with conditional formattting as you and Jon say. Would
want to think about it in MS Grump, heck, wouldn't even use MS Grump
unless a shotgun was pointed at my head, and I'd probably get it wrong
even then.

Seems to me that if one has lots of charts across many presentations
that I'd be a whole heap better off by just having a Private Sub that
gets called from a table drivern thing. Sub stays the same always and
the user maintains the Table, in where else some worksheet(s)
somewhere and we just read the variables each time and spit them at
the Private Sub routine. Kinda table driven programming like Rob Bovey
taught me to like and endorse.

Brian Reilly, PowerPoint MVP

On Sat, 22 Jan 2005 15:31:40 +0000, Andy Pope
wrote:

Hi,

Depending on the complexity you may be able to use a conditional chart.
http://www.andypope.info/ngs/ng32.htm

Although the data layout technique will work in MSGraph it would not be
dynamic unless you used some VBA code.

Cheers
Andy

Brian Reilly, MS MVP wrote:
I'm about to do this on a project where I want consistent colors by
"Brand Name" and I plan to use a VBA routine and a Select Case
statement on the "Brand Name" text and then apply the correct color
index. You just have to iterate throught the datapoints.

Brian Reilly, PowerPoint MVP




  #6   Report Post  
Old January 23rd 05, 04:49 PM
Andy Pope
 
Posts: n/a
Default

Hi Brian,

If you can use VBA then it will certainly be easier to apply changes to
many charts.

Cheers
Andy

wrote:
Hi Andy,
Could do this with conditional formattting as you and Jon say. Would
want to think about it in MS Grump, heck, wouldn't even use MS Grump
unless a shotgun was pointed at my head, and I'd probably get it wrong
even then.

Seems to me that if one has lots of charts across many presentations
that I'd be a whole heap better off by just having a Private Sub that
gets called from a table drivern thing. Sub stays the same always and
the user maintains the Table, in where else some worksheet(s)
somewhere and we just read the variables each time and spit them at
the Private Sub routine. Kinda table driven programming like Rob Bovey
taught me to like and endorse.

Brian Reilly, PowerPoint MVP

On Sat, 22 Jan 2005 15:31:40 +0000, Andy Pope
wrote:


Hi,

Depending on the complexity you may be able to use a conditional chart.
http://www.andypope.info/ngs/ng32.htm

Although the data layout technique will work in MSGraph it would not be
dynamic unless you used some VBA code.

Cheers
Andy

Brian Reilly, MS MVP wrote:

I'm about to do this on a project where I want consistent colors by
"Brand Name" and I plan to use a VBA routine and a Select Case
statement on the "Brand Name" text and then apply the correct color
index. You just have to iterate throught the datapoints.

Brian Reilly, PowerPoint MVP




--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
  #7   Report Post  
Old January 24th 05, 03:41 PM
Booger_Boy
 
Posts: n/a
Default

Most excellent! Thanks for your assistance.

I wasn't about to delve into VBA code, which I have found to be mind-numbing
in the past.

-BB.

"Andy Pope" wrote:

Hi,

Depending on the complexity you may be able to use a conditional chart.
http://www.andypope.info/ngs/ng32.htm

Although the data layout technique will work in MSGraph it would not be
dynamic unless you used some VBA code.

Cheers
Andy

Brian Reilly, MS MVP wrote:
I'm about to do this on a project where I want consistent colors by
"Brand Name" and I plan to use a VBA routine and a Select Case
statement on the "Brand Name" text and then apply the correct color
index. You just have to iterate throught the datapoints.

Brian Reilly, PowerPoint MVP


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Microsoft Query data in a pivot table. slapana Charts and Charting in Excel 0 January 11th 05 06:03 PM
changing proportion of chart and data table Dawn Parks Charts and Charting in Excel 3 January 6th 05 02:18 AM
multiple or changing colours in a data table on an excel chart fo. Kerri Buxton Charts and Charting in Excel 2 December 23rd 04 08:39 PM
Charting data ranges that change mikelee101 Charts and Charting in Excel 2 December 17th 04 12:07 AM
Why is the order of my data table opposite from graph? mozermodo Charts and Charting in Excel 5 December 7th 04 07:09 PM


All times are GMT +1. The time now is 02:45 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017