Excel - 2003 or previous.
Conditional formating on cells is useful but I would like to see this on
graphs as well.
For example in a bar graph chart the series colour can be set either
automatically or manually. A single data point can be set manually to a
different colour.
I would like to change a data point bar by condition / comparing to a value.
The application is to 'flag' a data point by colour change if the value
exceeds a certain comparison value.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...excel.charting

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

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


"Peter Shore" <Peter wrote in message
...
Excel - 2003 or previous.
Conditional formating on cells is useful but I would like to see this on
graphs as well.
For example in a bar graph chart the series colour can be set either
automatically or manually. A single data point can be set manually to a
different colour.
I would like to change a data point bar by condition / comparing to a
value.
The application is to 'flag' a data point by colour change if the value
exceeds a certain comparison value.

Jon,
I found your website and solution just minutes after posting my question. An
ingenious workround! The problem is that this makes the data table quite
complex. I would like to build in these formuales and then hide the cells but
as soon as you hide data it is no longer available for charts!
Another option would be to have the additional series and formulae in an
area outside of the print area and then view in 'print preview'
The table I have created is to be used by non excel experts so I have to
keep it as simple to use as possible and lock or conceal any formula to
prevent tampering!

"Jon Peltier" wrote:

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

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


"Peter Shore" <Peter wrote in message
...
Excel - 2003 or previous.
Conditional formating on cells is useful but I would like to see this on
graphs as well.
For example in a bar graph chart the series colour can be set either
automatically or manually. A single data point can be set manually to a
different colour.
I would like to change a data point bar by condition / comparing to a
value.
The application is to 'flag' a data point by colour change if the value
exceeds a certain comparison value.

Welcome to my world.

In general it's worth the effort to put your main data on a hidden
worksheet, and have the chart and any table in the display area link to this
data.

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


"Peter Shore" wrote in message
...
Jon,
I found your website and solution just minutes after posting my question.
An
ingenious workround! The problem is that this makes the data table quite
complex. I would like to build in these formuales and then hide the cells
but
as soon as you hide data it is no longer available for charts!
Another option would be to have the additional series and formulae in an
area outside of the print area and then view in 'print preview'
The table I have created is to be used by non excel experts so I have to
keep it as simple to use as possible and lock or conceal any formula to
prevent tampering!

"Jon Peltier" wrote:

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

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


"Peter Shore" <Peter wrote in message
...
Excel - 2003 or previous.
Conditional formating on cells is useful but I would like to see this
on
graphs as well.
For example in a bar graph chart the series colour can be set either
automatically or manually. A single data point can be set manually to a
different colour.
I would like to change a data point bar by condition / comparing to a
value.
The application is to 'flag' a data point by colour change if the value
exceeds a certain comparison value.

Jon,
Thank you for the help - I tried this last night and I have been able to
produce the chart I need. I have also combined Bars with Lines so the
threshold values that change the colour are displayed as a line across the
chart. Net result is that as a bar graph value meets or exceeds one of the
lines it changes colour.

NB: Another trick I employed that you may find useful (or you may already
know this) is to use a customised background on a bar graph to give me a
coloured scale.
eg from 0% to 80% green, from 80% to 90% amber and from 90% to 100% red.
I started by setting rows and columns to the same size for square cells,
filled 8 cells green, 1 amber and 1 red. copied and pasted this into 'paint'
to create a bitmap. Then I used the format chart, background - picture and
selected the bitmap I had just created. This scaled itself to the chart
background and gave me a bar graph with green up to 80% etc.


"Jon Peltier" wrote:

Welcome to my world.

In general it's worth the effort to put your main data on a hidden
worksheet, and have the chart and any table in the display area link to this
data.

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


"Peter Shore" wrote in message
...
Jon,
I found your website and solution just minutes after posting my question.
An
ingenious workround! The problem is that this makes the data table quite
complex. I would like to build in these formuales and then hide the cells
but
as soon as you hide data it is no longer available for charts!
Another option would be to have the additional series and formulae in an
area outside of the print area and then view in 'print preview'
The table I have created is to be used by non excel experts so I have to
keep it as simple to use as possible and lock or conceal any formula to
prevent tampering!

"Jon Peltier" wrote:

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

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


"Peter Shore" <Peter wrote in message
...
Excel - 2003 or previous.
Conditional formating on cells is useful but I would like to see this
on
graphs as well.
For example in a bar graph chart the series colour can be set either
automatically or manually. A single data point can be set manually to a
different colour.
I would like to change a data point bar by condition / comparing to a
value.
The application is to 'flag' a data point by colour change if the value
exceeds a certain comparison value.

You've seen my more flexible version, with stacked columns on the primary
axis and a regular column or line series on the secondary. When I get a
chance I'll post it as a web page.

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


"Peter Shore" wrote in message
...
Jon,
Thank you for the help - I tried this last night and I have been able to
produce the chart I need. I have also combined Bars with Lines so the
threshold values that change the colour are displayed as a line across the
chart. Net result is that as a bar graph value meets or exceeds one of the
lines it changes colour.

NB: Another trick I employed that you may find useful (or you may already
know this) is to use a customised background on a bar graph to give me a
coloured scale.
eg from 0% to 80% green, from 80% to 90% amber and from 90% to 100% red.
I started by setting rows and columns to the same size for square cells,
filled 8 cells green, 1 amber and 1 red. copied and pasted this into
'paint'
to create a bitmap. Then I used the format chart, background - picture and
selected the bitmap I had just created. This scaled itself to the chart
background and gave me a bar graph with green up to 80% etc.


"Jon Peltier" wrote:

Welcome to my world.

In general it's worth the effort to put your main data on a hidden
worksheet, and have the chart and any table in the display area link to
this
data.

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


"Peter Shore" wrote in message
...
Jon,
I found your website and solution just minutes after posting my
question.
An
ingenious workround! The problem is that this makes the data table
quite
complex. I would like to build in these formuales and then hide the
cells
but
as soon as you hide data it is no longer available for charts!
Another option would be to have the additional series and formulae in
an
area outside of the print area and then view in 'print preview'
The table I have created is to be used by non excel experts so I have
to
keep it as simple to use as possible and lock or conceal any formula to
prevent tampering!

"Jon Peltier" wrote:

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

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


"Peter Shore" <Peter wrote in message
...
Excel - 2003 or previous.
Conditional formating on cells is useful but I would like to see
this
on
graphs as well.
For example in a bar graph chart the series colour can be set either
automatically or manually. A single data point can be set manually
to a
different colour.
I would like to change a data point bar by condition / comparing to
a
value.
The application is to 'flag' a data point by colour change if the
value
exceeds a certain comparison value.