Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 8th 09, 01:52 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2008
Posts: 24
Default Automatic swap of x - and y - axis

I am managing a database of lab results for an external company. That compnay
recently upgraded from Excel 2003 to Excel 2007, which resulted in the
automatic swap of the x - and y - axis in all time - value graphs that are
inbeded in each worksheet of the database. I have labouriously reversed many
of the automatic changes, but still have a long way to go. However, the most
frustrating thing is that as soon as staff copy template worksheets, in which
the x - and y - axis have been corrected so that time is whown on the x -
axis and values on th y - axis, to enter data for a new product batch, the x
- and y - axis revert back the wrong way round. Trying to deal with this
problem caused by Excel 2007 has been one of the most frustrating work
experiences I have had for a very long time. Any ideas of what might help?
The other thing is that I can't try your suggestions striaght away, as,
luckily, I don't have Excel 2007; I can only try things, when I am working on
my client's system.

  #2   Report Post  
Old February 8th 09, 04:31 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Automatic swap of x - and y - axis

What kind of chart are we talking about here? Do you mean the chart converts
from horizontal bars to vertical or something like that? Or does the actual
data switch in the series formulas?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
I am managing a database of lab results for an external company. That
compnay
recently upgraded from Excel 2003 to Excel 2007, which resulted in the
automatic swap of the x - and y - axis in all time - value graphs that are
inbeded in each worksheet of the database. I have labouriously reversed
many
of the automatic changes, but still have a long way to go. However, the
most
frustrating thing is that as soon as staff copy template worksheets, in
which
the x - and y - axis have been corrected so that time is whown on the x -
axis and values on th y - axis, to enter data for a new product batch, the
x
- and y - axis revert back the wrong way round. Trying to deal with this
problem caused by Excel 2007 has been one of the most frustrating work
experiences I have had for a very long time. Any ideas of what might help?
The other thing is that I can't try your suggestions striaght away, as,
luckily, I don't have Excel 2007; I can only try things, when I am working
on
my client's system.



  #3   Report Post  
Old February 8th 09, 07:31 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2008
Posts: 24
Default Automatic swap of x - and y - axis

Jon
I use very simple line (mostly) and bar charts. The time line (usually
dates, covering between a few weeks to one year) is shown on the x - axis and
the values, e.g. temperatures in a composting pile, are shown on the y -
axis. The problem I am having is that the x - axis becomes the y-axis and
vice versa, messingup the chart and making it useless. This happened
automatically when Excel 2007 was installed, and it still happens when a
template (= worksheet established in Excel 2007) is copied within an Excel
document to create a new worksheet for a new product batch.

I can manually switch the x- and y - axis back so that the graph look o.k.
again, but this is a frustrating way of doing it, and there must be a way of
preventing this from happening.

Does this explain it better?

Johannes

"Jon Peltier" wrote:

What kind of chart are we talking about here? Do you mean the chart converts
from horizontal bars to vertical or something like that? Or does the actual
data switch in the series formulas?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
I am managing a database of lab results for an external company. That
compnay
recently upgraded from Excel 2003 to Excel 2007, which resulted in the
automatic swap of the x - and y - axis in all time - value graphs that are
inbeded in each worksheet of the database. I have labouriously reversed
many
of the automatic changes, but still have a long way to go. However, the
most
frustrating thing is that as soon as staff copy template worksheets, in
which
the x - and y - axis have been corrected so that time is whown on the x -
axis and values on th y - axis, to enter data for a new product batch, the
x
- and y - axis revert back the wrong way round. Trying to deal with this
problem caused by Excel 2007 has been one of the most frustrating work
experiences I have had for a very long time. Any ideas of what might help?
The other thing is that I can't try your suggestions striaght away, as,
luckily, I don't have Excel 2007; I can only try things, when I am working
on
my client's system.




  #4   Report Post  
Old February 8th 09, 03:12 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Automatic swap of x - and y - axis

I'm having trouble understanding "the x - axis becomes the y-axis". How does
the chart change its appearance? What steps do you take to "change it back"?

Are the values that were plotted on the horizontal axis now plotted on the
vertical axis, and the values that were plotted on the vertical axis now
plotted on the horizontal axis? Do the axis titles switch positions? Does a
(vertical) column chart become a (horizontal) bar chart?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
Jon
I use very simple line (mostly) and bar charts. The time line (usually
dates, covering between a few weeks to one year) is shown on the x - axis
and
the values, e.g. temperatures in a composting pile, are shown on the y -
axis. The problem I am having is that the x - axis becomes the y-axis and
vice versa, messingup the chart and making it useless. This happened
automatically when Excel 2007 was installed, and it still happens when a
template (= worksheet established in Excel 2007) is copied within an Excel
document to create a new worksheet for a new product batch.

I can manually switch the x- and y - axis back so that the graph look o.k.
again, but this is a frustrating way of doing it, and there must be a way
of
preventing this from happening.

Does this explain it better?

Johannes

"Jon Peltier" wrote:

What kind of chart are we talking about here? Do you mean the chart
converts
from horizontal bars to vertical or something like that? Or does the
actual
data switch in the series formulas?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
I am managing a database of lab results for an external company. That
compnay
recently upgraded from Excel 2003 to Excel 2007, which resulted in the
automatic swap of the x - and y - axis in all time - value graphs that
are
inbeded in each worksheet of the database. I have labouriously reversed
many
of the automatic changes, but still have a long way to go. However, the
most
frustrating thing is that as soon as staff copy template worksheets, in
which
the x - and y - axis have been corrected so that time is whown on the
x -
axis and values on th y - axis, to enter data for a new product batch,
the
x
- and y - axis revert back the wrong way round. Trying to deal with
this
problem caused by Excel 2007 has been one of the most frustrating work
experiences I have had for a very long time. Any ideas of what might
help?
The other thing is that I can't try your suggestions striaght away, as,
luckily, I don't have Excel 2007; I can only try things, when I am
working
on
my client's system.






  #5   Report Post  
Old February 14th 09, 03:16 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2008
Posts: 24
Default Automatic swap of x - and y - axis

Jon, sorry for my long silence.

What you are describing is exactely what happens, i.e. that the values on
the x-axis (horizontal) become the values on the y-axis (vertical). This
obviously messes up the the graph.

Unfortunately I can't quite remember what manual changes I make to get the
graph back to the way it used to be (because this problem occurs not on my
computer but on my clients computer where I go only once a month or so). I
think I am changing the graph back by going into the 'Source Data' Menue, and
then change the 'Series in' from Rows to Coloumns, or vice versa. I will be
going to my client again on Monday, and after I have made the change, I will
get back to you to tell you exactely what I do.

Johannes

"Jon Peltier" wrote:

I'm having trouble understanding "the x - axis becomes the y-axis". How does
the chart change its appearance? What steps do you take to "change it back"?

Are the values that were plotted on the horizontal axis now plotted on the
vertical axis, and the values that were plotted on the vertical axis now
plotted on the horizontal axis? Do the axis titles switch positions? Does a
(vertical) column chart become a (horizontal) bar chart?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
Jon
I use very simple line (mostly) and bar charts. The time line (usually
dates, covering between a few weeks to one year) is shown on the x - axis
and
the values, e.g. temperatures in a composting pile, are shown on the y -
axis. The problem I am having is that the x - axis becomes the y-axis and
vice versa, messingup the chart and making it useless. This happened
automatically when Excel 2007 was installed, and it still happens when a
template (= worksheet established in Excel 2007) is copied within an Excel
document to create a new worksheet for a new product batch.

I can manually switch the x- and y - axis back so that the graph look o.k.
again, but this is a frustrating way of doing it, and there must be a way
of
preventing this from happening.

Does this explain it better?

Johannes

"Jon Peltier" wrote:

What kind of chart are we talking about here? Do you mean the chart
converts
from horizontal bars to vertical or something like that? Or does the
actual
data switch in the series formulas?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
I am managing a database of lab results for an external company. That
compnay
recently upgraded from Excel 2003 to Excel 2007, which resulted in the
automatic swap of the x - and y - axis in all time - value graphs that
are
inbeded in each worksheet of the database. I have labouriously reversed
many
of the automatic changes, but still have a long way to go. However, the
most
frustrating thing is that as soon as staff copy template worksheets, in
which
the x - and y - axis have been corrected so that time is whown on the
x -
axis and values on th y - axis, to enter data for a new product batch,
the
x
- and y - axis revert back the wrong way round. Trying to deal with
this
problem caused by Excel 2007 has been one of the most frustrating work
experiences I have had for a very long time. Any ideas of what might
help?
The other thing is that I can't try your suggestions striaght away, as,
luckily, I don't have Excel 2007; I can only try things, when I am
working
on
my client's system.








  #6   Report Post  
Old February 15th 09, 05:15 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Automatic swap of x - and y - axis

What happened that made the chart switch? Did the client mess around with
it?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
Jon, sorry for my long silence.

What you are describing is exactely what happens, i.e. that the values on
the x-axis (horizontal) become the values on the y-axis (vertical). This
obviously messes up the the graph.

Unfortunately I can't quite remember what manual changes I make to get the
graph back to the way it used to be (because this problem occurs not on my
computer but on my clients computer where I go only once a month or so). I
think I am changing the graph back by going into the 'Source Data' Menue,
and
then change the 'Series in' from Rows to Coloumns, or vice versa. I will
be
going to my client again on Monday, and after I have made the change, I
will
get back to you to tell you exactely what I do.

Johannes

"Jon Peltier" wrote:

I'm having trouble understanding "the x - axis becomes the y-axis". How
does
the chart change its appearance? What steps do you take to "change it
back"?

Are the values that were plotted on the horizontal axis now plotted on
the
vertical axis, and the values that were plotted on the vertical axis now
plotted on the horizontal axis? Do the axis titles switch positions? Does
a
(vertical) column chart become a (horizontal) bar chart?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
Jon
I use very simple line (mostly) and bar charts. The time line (usually
dates, covering between a few weeks to one year) is shown on the x -
axis
and
the values, e.g. temperatures in a composting pile, are shown on the
y -
axis. The problem I am having is that the x - axis becomes the y-axis
and
vice versa, messingup the chart and making it useless. This happened
automatically when Excel 2007 was installed, and it still happens when
a
template (= worksheet established in Excel 2007) is copied within an
Excel
document to create a new worksheet for a new product batch.

I can manually switch the x- and y - axis back so that the graph look
o.k.
again, but this is a frustrating way of doing it, and there must be a
way
of
preventing this from happening.

Does this explain it better?

Johannes

"Jon Peltier" wrote:

What kind of chart are we talking about here? Do you mean the chart
converts
from horizontal bars to vertical or something like that? Or does the
actual
data switch in the series formulas?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
I am managing a database of lab results for an external company. That
compnay
recently upgraded from Excel 2003 to Excel 2007, which resulted in
the
automatic swap of the x - and y - axis in all time - value graphs
that
are
inbeded in each worksheet of the database. I have labouriously
reversed
many
of the automatic changes, but still have a long way to go. However,
the
most
frustrating thing is that as soon as staff copy template worksheets,
in
which
the x - and y - axis have been corrected so that time is whown on
the
x -
axis and values on th y - axis, to enter data for a new product
batch,
the
x
- and y - axis revert back the wrong way round. Trying to deal with
this
problem caused by Excel 2007 has been one of the most frustrating
work
experiences I have had for a very long time. Any ideas of what might
help?
The other thing is that I can't try your suggestions striaght away,
as,
luckily, I don't have Excel 2007; I can only try things, when I am
working
on
my client's system.








  #7   Report Post  
Old February 16th 09, 02:42 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2008
Posts: 24
Default Automatic swap of x - and y - axis

O.k. today I am at my cleints place and I have looked a bit more into it. I
have checked last years databases and graphs appear to be o.k. for the first
half of the year (until July / August) while those covering the rest of the
year are skewed. They upgraded to Excel 2007 in June/July. This means, that
old, existing graphs were not changed, but what has changed is that when the
MASTER Sheet (= a template for entering data, which then automatically
creates a time vs value graph) is copied for recording data from a new
product batch, the x - and y - axis switch. Therefore, all graphs after July
/ August are skewed. This problem persists to this day, meaning that every
new worksheet that is created contains skewed graphs, making the problem
(=workload) bigger.

I can correct the problem manually in the following way:
1. right click on the graph
2. Click 'Select data'
3. Click 'Switch Row / Column'
4. Click 'Switch Row / Column' again
5. Correct the format of the x-axis to its original format

However, there are many graphs, and if I am unable to solve the problem, we
will have to ommit using graphs.

If you are able to provide me with an email address, I can send you one of
the files, and you can have a look whether the same thing happens on your
computer, and if so, work out what the problem is.

Johannes

"Jon Peltier" wrote:

What happened that made the chart switch? Did the client mess around with
it?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
Jon, sorry for my long silence.

What you are describing is exactely what happens, i.e. that the values on
the x-axis (horizontal) become the values on the y-axis (vertical). This
obviously messes up the the graph.

Unfortunately I can't quite remember what manual changes I make to get the
graph back to the way it used to be (because this problem occurs not on my
computer but on my clients computer where I go only once a month or so). I
think I am changing the graph back by going into the 'Source Data' Menue,
and
then change the 'Series in' from Rows to Coloumns, or vice versa. I will
be
going to my client again on Monday, and after I have made the change, I
will
get back to you to tell you exactely what I do.

Johannes

"Jon Peltier" wrote:

I'm having trouble understanding "the x - axis becomes the y-axis". How
does
the chart change its appearance? What steps do you take to "change it
back"?

Are the values that were plotted on the horizontal axis now plotted on
the
vertical axis, and the values that were plotted on the vertical axis now
plotted on the horizontal axis? Do the axis titles switch positions? Does
a
(vertical) column chart become a (horizontal) bar chart?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
Jon
I use very simple line (mostly) and bar charts. The time line (usually
dates, covering between a few weeks to one year) is shown on the x -
axis
and
the values, e.g. temperatures in a composting pile, are shown on the
y -
axis. The problem I am having is that the x - axis becomes the y-axis
and
vice versa, messingup the chart and making it useless. This happened
automatically when Excel 2007 was installed, and it still happens when
a
template (= worksheet established in Excel 2007) is copied within an
Excel
document to create a new worksheet for a new product batch.

I can manually switch the x- and y - axis back so that the graph look
o.k.
again, but this is a frustrating way of doing it, and there must be a
way
of
preventing this from happening.

Does this explain it better?

Johannes

"Jon Peltier" wrote:

What kind of chart are we talking about here? Do you mean the chart
converts
from horizontal bars to vertical or something like that? Or does the
actual
data switch in the series formulas?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
I am managing a database of lab results for an external company. That
compnay
recently upgraded from Excel 2003 to Excel 2007, which resulted in
the
automatic swap of the x - and y - axis in all time - value graphs
that
are
inbeded in each worksheet of the database. I have labouriously
reversed
many
of the automatic changes, but still have a long way to go. However,
the
most
frustrating thing is that as soon as staff copy template worksheets,
in
which
the x - and y - axis have been corrected so that time is whown on
the
x -
axis and values on th y - axis, to enter data for a new product
batch,
the
x
- and y - axis revert back the wrong way round. Trying to deal with
this
problem caused by Excel 2007 has been one of the most frustrating
work
experiences I have had for a very long time. Any ideas of what might
help?
The other thing is that I can't try your suggestions striaght away,
as,
luckily, I don't have Excel 2007; I can only try things, when I am
working
on
my client's system.









  #8   Report Post  
Old February 16th 09, 03:39 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Automatic swap of x - and y - axis

Could you fix the template in the master sheet? Or does simply changing the
data cause the chart to change?

An alternative might be to run a macro. This will change charts in the
active workbook:

Sub FixCharts()
Dim sh As Object
Dim cht As Chart
Dim chobj As ChartObject

For Each cht In ActiveWorkbook.Charts
cht.PlotBy = xlColumns ''' or xlRows ''' ***
Next
For Each sh In ActiveWorkbook.Sheets
For Each chobj In sh.ChartObjects
chobj.Chart.PlotBy = xlColumns ''' or xlRows ''' ***
Next
Next

End Sub

In the two places highlighted by *** you need to make sure the appropriate
alignment is being specified. See this post if you aren't sure how to use
this code:
http://peltiertech.com/WordPress/how...e-elses-macro/

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
O.k. today I am at my cleints place and I have looked a bit more into it.
I
have checked last years databases and graphs appear to be o.k. for the
first
half of the year (until July / August) while those covering the rest of
the
year are skewed. They upgraded to Excel 2007 in June/July. This means,
that
old, existing graphs were not changed, but what has changed is that when
the
MASTER Sheet (= a template for entering data, which then automatically
creates a time vs value graph) is copied for recording data from a new
product batch, the x - and y - axis switch. Therefore, all graphs after
July
/ August are skewed. This problem persists to this day, meaning that every
new worksheet that is created contains skewed graphs, making the problem
(=workload) bigger.

I can correct the problem manually in the following way:
1. right click on the graph
2. Click 'Select data'
3. Click 'Switch Row / Column'
4. Click 'Switch Row / Column' again
5. Correct the format of the x-axis to its original format

However, there are many graphs, and if I am unable to solve the problem,
we
will have to ommit using graphs.

If you are able to provide me with an email address, I can send you one of
the files, and you can have a look whether the same thing happens on your
computer, and if so, work out what the problem is.

Johannes

"Jon Peltier" wrote:

What happened that made the chart switch? Did the client mess around with
it?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
Jon, sorry for my long silence.

What you are describing is exactely what happens, i.e. that the values
on
the x-axis (horizontal) become the values on the y-axis (vertical).
This
obviously messes up the the graph.

Unfortunately I can't quite remember what manual changes I make to get
the
graph back to the way it used to be (because this problem occurs not on
my
computer but on my clients computer where I go only once a month or
so). I
think I am changing the graph back by going into the 'Source Data'
Menue,
and
then change the 'Series in' from Rows to Coloumns, or vice versa. I
will
be
going to my client again on Monday, and after I have made the change, I
will
get back to you to tell you exactely what I do.

Johannes

"Jon Peltier" wrote:

I'm having trouble understanding "the x - axis becomes the y-axis".
How
does
the chart change its appearance? What steps do you take to "change it
back"?

Are the values that were plotted on the horizontal axis now plotted on
the
vertical axis, and the values that were plotted on the vertical axis
now
plotted on the horizontal axis? Do the axis titles switch positions?
Does
a
(vertical) column chart become a (horizontal) bar chart?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
Jon
I use very simple line (mostly) and bar charts. The time line
(usually
dates, covering between a few weeks to one year) is shown on the x -
axis
and
the values, e.g. temperatures in a composting pile, are shown on the
y -
axis. The problem I am having is that the x - axis becomes the
y-axis
and
vice versa, messingup the chart and making it useless. This happened
automatically when Excel 2007 was installed, and it still happens
when
a
template (= worksheet established in Excel 2007) is copied within an
Excel
document to create a new worksheet for a new product batch.

I can manually switch the x- and y - axis back so that the graph
look
o.k.
again, but this is a frustrating way of doing it, and there must be
a
way
of
preventing this from happening.

Does this explain it better?

Johannes

"Jon Peltier" wrote:

What kind of chart are we talking about here? Do you mean the chart
converts
from horizontal bars to vertical or something like that? Or does
the
actual
data switch in the series formulas?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
I am managing a database of lab results for an external company.
That
compnay
recently upgraded from Excel 2003 to Excel 2007, which resulted
in
the
automatic swap of the x - and y - axis in all time - value graphs
that
are
inbeded in each worksheet of the database. I have labouriously
reversed
many
of the automatic changes, but still have a long way to go.
However,
the
most
frustrating thing is that as soon as staff copy template
worksheets,
in
which
the x - and y - axis have been corrected so that time is whown on
the
x -
axis and values on th y - axis, to enter data for a new product
batch,
the
x
- and y - axis revert back the wrong way round. Trying to deal
with
this
problem caused by Excel 2007 has been one of the most frustrating
work
experiences I have had for a very long time. Any ideas of what
might
help?
The other thing is that I can't try your suggestions striaght
away,
as,
luckily, I don't have Excel 2007; I can only try things, when I
am
working
on
my client's system.











  #9   Report Post  
Old February 16th 09, 09:38 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2008
Posts: 24
Default Automatic swap of x - and y - axis

I have modified the template by re-drawing the graph, but it didn't alleviate
the problem. The graph changes not when data are entered, but when the
template is copied to create a new worksheet. How it works is that we have a
template and then, say 20 or 30 worksheets, which represent individual
product batches. If a new product batch is produced today, laboratory staff
copy the template to create a new worksheet and then enter the recorded data
for that product batch. This means that, by the time the graph is copied, it
does not contain any data. We have tried to enter data, e.g. the date 1.
January, into the template, and that did help in some cases, but not all.

The macro looks complicated to me, I have never done a macro, but I'll have
a look at it. However, I probably won't be back at my clients place for 3 to
4 weeks, before I can have a look at it again.

Johannes



"Jon Peltier" wrote:

Could you fix the template in the master sheet? Or does simply changing the
data cause the chart to change?

An alternative might be to run a macro. This will change charts in the
active workbook:

Sub FixCharts()
Dim sh As Object
Dim cht As Chart
Dim chobj As ChartObject

For Each cht In ActiveWorkbook.Charts
cht.PlotBy = xlColumns ''' or xlRows ''' ***
Next
For Each sh In ActiveWorkbook.Sheets
For Each chobj In sh.ChartObjects
chobj.Chart.PlotBy = xlColumns ''' or xlRows ''' ***
Next
Next

End Sub

In the two places highlighted by *** you need to make sure the appropriate
alignment is being specified. See this post if you aren't sure how to use
this code:
http://peltiertech.com/WordPress/how...e-elses-macro/

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
O.k. today I am at my cleints place and I have looked a bit more into it.
I
have checked last years databases and graphs appear to be o.k. for the
first
half of the year (until July / August) while those covering the rest of
the
year are skewed. They upgraded to Excel 2007 in June/July. This means,
that
old, existing graphs were not changed, but what has changed is that when
the
MASTER Sheet (= a template for entering data, which then automatically
creates a time vs value graph) is copied for recording data from a new
product batch, the x - and y - axis switch. Therefore, all graphs after
July
/ August are skewed. This problem persists to this day, meaning that every
new worksheet that is created contains skewed graphs, making the problem
(=workload) bigger.

I can correct the problem manually in the following way:
1. right click on the graph
2. Click 'Select data'
3. Click 'Switch Row / Column'
4. Click 'Switch Row / Column' again
5. Correct the format of the x-axis to its original format

However, there are many graphs, and if I am unable to solve the problem,
we
will have to ommit using graphs.

If you are able to provide me with an email address, I can send you one of
the files, and you can have a look whether the same thing happens on your
computer, and if so, work out what the problem is.

Johannes

"Jon Peltier" wrote:

What happened that made the chart switch? Did the client mess around with
it?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
Jon, sorry for my long silence.

What you are describing is exactely what happens, i.e. that the values
on
the x-axis (horizontal) become the values on the y-axis (vertical).
This
obviously messes up the the graph.

Unfortunately I can't quite remember what manual changes I make to get
the
graph back to the way it used to be (because this problem occurs not on
my
computer but on my clients computer where I go only once a month or
so). I
think I am changing the graph back by going into the 'Source Data'
Menue,
and
then change the 'Series in' from Rows to Coloumns, or vice versa. I
will
be
going to my client again on Monday, and after I have made the change, I
will
get back to you to tell you exactely what I do.

Johannes

"Jon Peltier" wrote:

I'm having trouble understanding "the x - axis becomes the y-axis".
How
does
the chart change its appearance? What steps do you take to "change it
back"?

Are the values that were plotted on the horizontal axis now plotted on
the
vertical axis, and the values that were plotted on the vertical axis
now
plotted on the horizontal axis? Do the axis titles switch positions?
Does
a
(vertical) column chart become a (horizontal) bar chart?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
Jon
I use very simple line (mostly) and bar charts. The time line
(usually
dates, covering between a few weeks to one year) is shown on the x -
axis
and
the values, e.g. temperatures in a composting pile, are shown on the
y -
axis. The problem I am having is that the x - axis becomes the
y-axis
and
vice versa, messingup the chart and making it useless. This happened
automatically when Excel 2007 was installed, and it still happens
when
a
template (= worksheet established in Excel 2007) is copied within an
Excel
document to create a new worksheet for a new product batch.

I can manually switch the x- and y - axis back so that the graph
look
o.k.
again, but this is a frustrating way of doing it, and there must be
a
way
of
preventing this from happening.

Does this explain it better?

Johannes

"Jon Peltier" wrote:

What kind of chart are we talking about here? Do you mean the chart
converts
from horizontal bars to vertical or something like that? Or does
the
actual
data switch in the series formulas?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
I am managing a database of lab results for an external company.
That
compnay
recently upgraded from Excel 2003 to Excel 2007, which resulted
in
the
automatic swap of the x - and y - axis in all time - value graphs
that
are
inbeded in each worksheet of the database. I have labouriously
reversed
many
of the automatic changes, but still have a long way to go.
However,
the
most
frustrating thing is that as soon as staff copy template
worksheets,
in
which
the x - and y - axis have been corrected so that time is whown on
the
x -
axis and values on th y - axis, to enter data for a new product
batch,
the
x
- and y - axis revert back the wrong way round. Trying to deal
with
this
problem caused by Excel 2007 has been one of the most frustrating
work
experiences I have had for a very long time. Any ideas of what
might
help?
The other thing is that I can't try your suggestions striaght
away,
as,
luckily, I don't have Excel 2007; I can only try things, when I
am
working
on
my client's system.












  #10   Report Post  
Old February 17th 09, 02:11 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Automatic swap of x - and y - axis

Johannes -

If you could provide a (sanitized) sample of data and a more detailed
description of the chart, someone here may be able to offer a suggestion
that would keep the chart in its intended configuration.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
I have modified the template by re-drawing the graph, but it didn't
alleviate
the problem. The graph changes not when data are entered, but when the
template is copied to create a new worksheet. How it works is that we have
a
template and then, say 20 or 30 worksheets, which represent individual
product batches. If a new product batch is produced today, laboratory
staff
copy the template to create a new worksheet and then enter the recorded
data
for that product batch. This means that, by the time the graph is copied,
it
does not contain any data. We have tried to enter data, e.g. the date 1.
January, into the template, and that did help in some cases, but not all.

The macro looks complicated to me, I have never done a macro, but I'll
have
a look at it. However, I probably won't be back at my clients place for 3
to
4 weeks, before I can have a look at it again.

Johannes



"Jon Peltier" wrote:

Could you fix the template in the master sheet? Or does simply changing
the
data cause the chart to change?

An alternative might be to run a macro. This will change charts in the
active workbook:

Sub FixCharts()
Dim sh As Object
Dim cht As Chart
Dim chobj As ChartObject

For Each cht In ActiveWorkbook.Charts
cht.PlotBy = xlColumns ''' or xlRows ''' ***
Next
For Each sh In ActiveWorkbook.Sheets
For Each chobj In sh.ChartObjects
chobj.Chart.PlotBy = xlColumns ''' or xlRows ''' ***
Next
Next

End Sub

In the two places highlighted by *** you need to make sure the
appropriate
alignment is being specified. See this post if you aren't sure how to use
this code:
http://peltiertech.com/WordPress/how...e-elses-macro/

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
O.k. today I am at my cleints place and I have looked a bit more into
it.
I
have checked last years databases and graphs appear to be o.k. for the
first
half of the year (until July / August) while those covering the rest of
the
year are skewed. They upgraded to Excel 2007 in June/July. This means,
that
old, existing graphs were not changed, but what has changed is that
when
the
MASTER Sheet (= a template for entering data, which then automatically
creates a time vs value graph) is copied for recording data from a new
product batch, the x - and y - axis switch. Therefore, all graphs after
July
/ August are skewed. This problem persists to this day, meaning that
every
new worksheet that is created contains skewed graphs, making the
problem
(=workload) bigger.

I can correct the problem manually in the following way:
1. right click on the graph
2. Click 'Select data'
3. Click 'Switch Row / Column'
4. Click 'Switch Row / Column' again
5. Correct the format of the x-axis to its original format

However, there are many graphs, and if I am unable to solve the
problem,
we
will have to ommit using graphs.

If you are able to provide me with an email address, I can send you one
of
the files, and you can have a look whether the same thing happens on
your
computer, and if so, work out what the problem is.

Johannes

"Jon Peltier" wrote:

What happened that made the chart switch? Did the client mess around
with
it?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
Jon, sorry for my long silence.

What you are describing is exactely what happens, i.e. that the
values
on
the x-axis (horizontal) become the values on the y-axis (vertical).
This
obviously messes up the the graph.

Unfortunately I can't quite remember what manual changes I make to
get
the
graph back to the way it used to be (because this problem occurs not
on
my
computer but on my clients computer where I go only once a month or
so). I
think I am changing the graph back by going into the 'Source Data'
Menue,
and
then change the 'Series in' from Rows to Coloumns, or vice versa. I
will
be
going to my client again on Monday, and after I have made the
change, I
will
get back to you to tell you exactely what I do.

Johannes

"Jon Peltier" wrote:

I'm having trouble understanding "the x - axis becomes the y-axis".
How
does
the chart change its appearance? What steps do you take to "change
it
back"?

Are the values that were plotted on the horizontal axis now plotted
on
the
vertical axis, and the values that were plotted on the vertical
axis
now
plotted on the horizontal axis? Do the axis titles switch
positions?
Does
a
(vertical) column chart become a (horizontal) bar chart?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
Jon
I use very simple line (mostly) and bar charts. The time line
(usually
dates, covering between a few weeks to one year) is shown on the
x -
axis
and
the values, e.g. temperatures in a composting pile, are shown on
the
y -
axis. The problem I am having is that the x - axis becomes the
y-axis
and
vice versa, messingup the chart and making it useless. This
happened
automatically when Excel 2007 was installed, and it still happens
when
a
template (= worksheet established in Excel 2007) is copied within
an
Excel
document to create a new worksheet for a new product batch.

I can manually switch the x- and y - axis back so that the graph
look
o.k.
again, but this is a frustrating way of doing it, and there must
be
a
way
of
preventing this from happening.

Does this explain it better?

Johannes

"Jon Peltier" wrote:

What kind of chart are we talking about here? Do you mean the
chart
converts
from horizontal bars to vertical or something like that? Or does
the
actual
data switch in the series formulas?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Johannes" wrote in message
...
I am managing a database of lab results for an external
company.
That
compnay
recently upgraded from Excel 2003 to Excel 2007, which
resulted
in
the
automatic swap of the x - and y - axis in all time - value
graphs
that
are
inbeded in each worksheet of the database. I have labouriously
reversed
many
of the automatic changes, but still have a long way to go.
However,
the
most
frustrating thing is that as soon as staff copy template
worksheets,
in
which
the x - and y - axis have been corrected so that time is whown
on
the
x -
axis and values on th y - axis, to enter data for a new
product
batch,
the
x
- and y - axis revert back the wrong way round. Trying to deal
with
this
problem caused by Excel 2007 has been one of the most
frustrating
work
experiences I have had for a very long time. Any ideas of what
might
help?
The other thing is that I can't try your suggestions striaght
away,
as,
luckily, I don't have Excel 2007; I can only try things, when
I
am
working
on
my client's system.
















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
Swap Y-Axis numbers kwinston Charts and Charting in Excel 2 April 18th 08 05:53 PM
Automatic Y axis scaling Paul van Coller Charts and Charting in Excel 1 March 26th 08 10:35 AM
How to change y axis automatic time range? Mike Charts and Charting in Excel 1 September 21st 06 07:06 PM
Automatic axis format jos Charts and Charting in Excel 1 February 10th 05 11:28 AM
Automatic Change of X-axis Jan Charts and Charting in Excel 3 January 16th 05 08:01 PM


All times are GMT +1. The time now is 06:52 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017