Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.charting
|
|||
|
|||
Automatic swap of x - and y - axis
Hi Jon
sorry for the long break in communication, but I haven't been back to my clients for more than a month. However, I will go there on Wednesday, and then I will forward one of the files, so you or your colleagues can have a look and hopefully figure out what the problem is and how to rectify it. Is it possible to attach a file to a posting on this website, and if yes, how do I do it? If not, what email address should I sent the file? Thanks Johannes "Jon Peltier" wrote: 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 |
#12
Posted to microsoft.public.excel.charting
|
|||
|
|||
Automatic swap of x - and y - axis
Jon
I have the 'corrupt' file. Where can I sent it? Johannes "Johannes" wrote: Hi Jon sorry for the long break in communication, but I haven't been back to my clients for more than a month. However, I will go there on Wednesday, and then I will forward one of the files, so you or your colleagues can have a look and hopefully figure out what the problem is and how to rectify it. Is it possible to attach a file to a posting on this website, and if yes, how do I do it? If not, what email address should I sent the file? Thanks Johannes "Jon Peltier" wrote: 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 |
#13
Posted to microsoft.public.excel.charting
|
|||
|
|||
Automatic swap of x - and y - axis
If the file is corrupt, you should try rebuilding it from scratch in a new
workbook. If you want to post some typical data as text in a new thread, a description of the chart, and a description of the problem, someone may be able to help. Make sure to describe the charts themselves and the problem clearly. I'm not sure why the 15-line macro seems so complicated. You could try it on a copy of your file, using instructions he http://peltiertech.com/WordPress/how...e-elses-macro/ - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - Training in Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Johannes" wrote in message ... Jon I have the 'corrupt' file. Where can I sent it? Johannes "Johannes" wrote: Hi Jon sorry for the long break in communication, but I haven't been back to my clients for more than a month. However, I will go there on Wednesday, and then I will forward one of the files, so you or your colleagues can have a look and hopefully figure out what the problem is and how to rectify it. Is it possible to attach a file to a posting on this website, and if yes, how do I do it? If not, what email address should I sent the file? Thanks Johannes "Jon Peltier" wrote: 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 |
#14
Posted to microsoft.public.excel.charting
|
|||
|
|||
Automatic swap of x - and y - axis
The file is not corrupt. The problem started simultanously in soem 30 files
when my client installed Excel 2007. I established a new file, trying to overcome the problem, but it didn't work, the problem remianed. Dealing with the macro might be no problem for you but it is for me - I am not a computer person, I am simply trying to use a Microsoft product, and trying to overcome a problem this new product is causing. By the same tooken, I would like to ask, why is it not possible that I forward one of the files where the problem occurs and than you or yoru colleagues can see exactely what the problem is? During our previous communication I have tried to explain to you what the problem is. My client has one option to overcome the problem, and that is to continue using the old Excel program, but that can be hardly the purpose of purchasing new Microsoft products. From my point of view, the easiest and most effective way of dealing with this is if I can send you a file with last years data and you can see that it everything worked o.k. from January to July (approx) and than from August onwards the problem occured, which marked the time when the new Excel program was installed. Please advise to which email address I can send the file. Johannes "Jon Peltier" wrote: If the file is corrupt, you should try rebuilding it from scratch in a new workbook. If you want to post some typical data as text in a new thread, a description of the chart, and a description of the problem, someone may be able to help. Make sure to describe the charts themselves and the problem clearly. I'm not sure why the 15-line macro seems so complicated. You could try it on a copy of your file, using instructions he http://peltiertech.com/WordPress/how...e-elses-macro/ - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - Training in Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Johannes" wrote in message ... Jon I have the 'corrupt' file. Where can I sent it? Johannes "Johannes" wrote: Hi Jon sorry for the long break in communication, but I haven't been back to my clients for more than a month. However, I will go there on Wednesday, and then I will forward one of the files, so you or your colleagues can have a look and hopefully figure out what the problem is and how to rectify it. Is it possible to attach a file to a posting on this website, and if yes, how do I do it? If not, what email address should I sent the file? Thanks Johannes "Jon Peltier" wrote: 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 - |
#15
Posted to microsoft.public.excel.charting
|
|||
|
|||
Automatic swap of x - and y - axis
Well, I can't promise to give it a lot of attention. I'm falling behind in
my regular work and shouldn't be spending so much time in the forums. Take my email from my profile here, and remove the capital letters. - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - Training in Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Johannes" wrote in message ... The file is not corrupt. The problem started simultanously in soem 30 files when my client installed Excel 2007. I established a new file, trying to overcome the problem, but it didn't work, the problem remianed. Dealing with the macro might be no problem for you but it is for me - I am not a computer person, I am simply trying to use a Microsoft product, and trying to overcome a problem this new product is causing. By the same tooken, I would like to ask, why is it not possible that I forward one of the files where the problem occurs and than you or yoru colleagues can see exactely what the problem is? During our previous communication I have tried to explain to you what the problem is. My client has one option to overcome the problem, and that is to continue using the old Excel program, but that can be hardly the purpose of purchasing new Microsoft products. From my point of view, the easiest and most effective way of dealing with this is if I can send you a file with last years data and you can see that it everything worked o.k. from January to July (approx) and than from August onwards the problem occured, which marked the time when the new Excel program was installed. Please advise to which email address I can send the file. Johannes "Jon Peltier" wrote: If the file is corrupt, you should try rebuilding it from scratch in a new workbook. If you want to post some typical data as text in a new thread, a description of the chart, and a description of the problem, someone may be able to help. Make sure to describe the charts themselves and the problem clearly. I'm not sure why the 15-line macro seems so complicated. You could try it on a copy of your file, using instructions he http://peltiertech.com/WordPress/how...e-elses-macro/ - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - Training in Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Johannes" wrote in message ... Jon I have the 'corrupt' file. Where can I sent it? Johannes "Johannes" wrote: Hi Jon sorry for the long break in communication, but I haven't been back to my clients for more than a month. However, I will go there on Wednesday, and then I will forward one of the files, so you or your colleagues can have a look and hopefully figure out what the problem is and how to rectify it. Is it possible to attach a file to a posting on this website, and if yes, how do I do it? If not, what email address should I sent the file? Thanks Johannes "Jon Peltier" wrote: 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 - |
#16
Posted to microsoft.public.excel.charting
|
|||
|
|||
Automatic swap of x - and y - axis
Thanks Jon, I will send the file with some explanation to your email.
I had assumed you are paid by Microsoft for dealing with their customer's complaints in this forum. If that is not the case I apologise for assuming so, and would like to thank you even more for volunteering your time. Johannes "Jon Peltier" wrote: Well, I can't promise to give it a lot of attention. I'm falling behind in my regular work and shouldn't be spending so much time in the forums. Take my email from my profile here, and remove the capital letters. - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - Training in Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Johannes" wrote in message ... The file is not corrupt. The problem started simultanously in soem 30 files when my client installed Excel 2007. I established a new file, trying to overcome the problem, but it didn't work, the problem remianed. Dealing with the macro might be no problem for you but it is for me - I am not a computer person, I am simply trying to use a Microsoft product, and trying to overcome a problem this new product is causing. By the same tooken, I would like to ask, why is it not possible that I forward one of the files where the problem occurs and than you or yoru colleagues can see exactely what the problem is? During our previous communication I have tried to explain to you what the problem is. My client has one option to overcome the problem, and that is to continue using the old Excel program, but that can be hardly the purpose of purchasing new Microsoft products. From my point of view, the easiest and most effective way of dealing with this is if I can send you a file with last years data and you can see that it everything worked o.k. from January to July (approx) and than from August onwards the problem occured, which marked the time when the new Excel program was installed. Please advise to which email address I can send the file. Johannes "Jon Peltier" wrote: If the file is corrupt, you should try rebuilding it from scratch in a new workbook. If you want to post some typical data as text in a new thread, a description of the chart, and a description of the problem, someone may be able to help. Make sure to describe the charts themselves and the problem clearly. I'm not sure why the 15-line macro seems so complicated. You could try it on a copy of your file, using instructions he http://peltiertech.com/WordPress/how...e-elses-macro/ - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - Training in Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Johannes" wrote in message ... Jon I have the 'corrupt' file. Where can I sent it? Johannes "Johannes" wrote: Hi Jon sorry for the long break in communication, but I haven't been back to my clients for more than a month. However, I will go there on Wednesday, and then I will forward one of the files, so you or your colleagues can have a look and hopefully figure out what the problem is and how to rectify it. Is it possible to attach a file to a posting on this website, and if yes, how do I do it? If not, what email address should I sent the file? Thanks Johannes "Jon Peltier" wrote: 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 |
#17
Posted to microsoft.public.excel.charting
|
|||
|
|||
Automatic swap of x - and y - axis
I am not an employee of Microsoft, nor am I paid to answer questions. I have
received the MVP award in recognition for having answered these questions in the past. In any case, I have responded to your email. When you assign a range to a chart, Excel tries to figure out what part of the range to use for series names, categories or X values, and Y values. Excel 2003 may have done this a bit more intelligently than Excel 2007 does now. Excel 2003 could recognize if the first row or column contained date-formatted values, and realize these were the X values. Excel 2007 seems not to have done this with your charts, perhaps because the data range is not contiguous. The error is that the dates are considered by Excel to be another set of Y values, not the X values as was intended. To fix existing charts in 2007, I found a simple approach. Select the chart, and on the Chart Tools Design tab, click on the Switch Row/Column button twice. This is enough to force Excel to re-evaluate the source data range, and this time Excel gets it right. The format of the changed X axis is not the same as in the "good" charts. You can copy one of those charts, select one that needs the axis fixed, and use Paste Special Formats. Future conversions may be improved by clearing the top left cell ("DATE"), because a blank cell helps Excel figure out that the first row and column are for different purposes. - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - Training in Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Johannes" wrote in message ... Thanks Jon, I will send the file with some explanation to your email. I had assumed you are paid by Microsoft for dealing with their customer's complaints in this forum. If that is not the case I apologise for assuming so, and would like to thank you even more for volunteering your time. Johannes "Jon Peltier" wrote: Well, I can't promise to give it a lot of attention. I'm falling behind in my regular work and shouldn't be spending so much time in the forums. Take my email from my profile here, and remove the capital letters. - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - Training in Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Johannes" wrote in message ... The file is not corrupt. The problem started simultanously in soem 30 files when my client installed Excel 2007. I established a new file, trying to overcome the problem, but it didn't work, the problem remianed. Dealing with the macro might be no problem for you but it is for me - I am not a computer person, I am simply trying to use a Microsoft product, and trying to overcome a problem this new product is causing. By the same tooken, I would like to ask, why is it not possible that I forward one of the files where the problem occurs and than you or yoru colleagues can see exactely what the problem is? During our previous communication I have tried to explain to you what the problem is. My client has one option to overcome the problem, and that is to continue using the old Excel program, but that can be hardly the purpose of purchasing new Microsoft products. From my point of view, the easiest and most effective way of dealing with this is if I can send you a file with last years data and you can see that it everything worked o.k. from January to July (approx) and than from August onwards the problem occured, which marked the time when the new Excel program was installed. Please advise to which email address I can send the file. Johannes "Jon Peltier" wrote: If the file is corrupt, you should try rebuilding it from scratch in a new workbook. If you want to post some typical data as text in a new thread, a description of the chart, and a description of the problem, someone may be able to help. Make sure to describe the charts themselves and the problem clearly. I'm not sure why the 15-line macro seems so complicated. You could try it on a copy of your file, using instructions he http://peltiertech.com/WordPress/how...e-elses-macro/ - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - Training in Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Johannes" wrote in message ... Jon I have the 'corrupt' file. Where can I sent it? Johannes "Johannes" wrote: Hi Jon sorry for the long break in communication, but I haven't been back to my clients for more than a month. However, I will go there on Wednesday, and then I will forward one of the files, so you or your colleagues can have a look and hopefully figure out what the problem is and how to rectify it. Is it possible to attach a file to a posting on this website, and if yes, how do I do it? If not, what email address should I sent the file? Thanks Johannes "Jon Peltier" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Swap Y-Axis numbers | Charts and Charting in Excel | |||
Automatic Y axis scaling | Charts and Charting in Excel | |||
How to change y axis automatic time range? | Charts and Charting in Excel | |||
Automatic axis format | Charts and Charting in Excel | |||
Automatic Change of X-axis | Charts and Charting in Excel |