View original (French)

Graphic copy issue in Excel 2007

Solved
Michel 4286 -  
 zaza -
Hello,

I bought Office 2007, everything is working fine except for Excel. What a hassle.
I created several charts based on data on sheet #1.
When I try to copy this sheet into my workbook, an inexplicable problem occurs:
on the new sheets, the charts no longer update, and the new data I enter has no effect on my charts.
Can you help me?

Thank you in advance.
Configuration: Windows Vista Internet Explorer 7.0

8 réponses

Réponse 1 / 8
Raymond PENTIER Posted messages 58550 Registration date   Status Contributeur Last intervention   17 475
 
Of course! If you copy and paste from sheet 1 to a blank sheet, you'll find the same chart a second time in this other sheet; and of course, with the original data. When you add data to this sheet, Excel has no reason to change its references!

So, you shouldn't copy and paste, but duplicate the sheet:

* Right-click on the tab of sheet 1, choose "Move or Copy...", check "Create a copy" and then "OK"
* Replace the data with the new values: the chart follows...

In general, it is strongly discouraged to copy and paste to reproduce a table or a sheet on another sheet or in another workbook; always use "Create a copy", which also helps to preserve row heights, column widths, cell formats, conditional formatting, names of ranges, etc...
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂
18
Michel 4286
 
Thank you for your response.
I wasn't precise; I did follow the method you outlined, I duplicated the sheet, and that's where the problem starts. On the duplicated sheet, the chart was reproduced, but it doesn't update when the data changes.
I've spent a lot of time looking for a solution. In vain. That's why I'm reaching out for help.

Good evening everyone.
0
Flo
 
Thank youuuuuuuuuu
0
zaza
 
Thank you so much, after a fierce struggle and my teacher telling me it was impossible to keep my chart colors and just change the data. I then used your method and thanks to you, I have a second chart identical to the first but with new data =)
Have a great end of the day!!
0
Réponse 2 / 8
Le Pingou Posted messages 12375 Registration date   Status Contributeur Last intervention   1 476
 
Hello Raymond,
Well, I'm getting the same result as Michel 4286, on the duplicated sheet the data range for the chart is empty?
Where's the mistake, thank you for your insights...
--

Regards.
Jean-Pierre
1
Raymond PENTIER Posted messages 58550 Registration date   Status Contributeur Last intervention   17 475
 
Hi, J-P!

There’s no mistake, you need to do exactly what I said 
* Right-click on the tab of sheet 1, choose "Move or Copy...", check "Create a copy" and then "OK" * Replace the data with the new values: the graph will follow...
If your original sheet is named Sheet1 and you rename the duplicated sheet to F2,
with an original graph =SERIES(Sheet1!$C$1;;Sheet1!$C$2:$C$13;1)
you will get the graph =SERIES('F2'!$C$1;;'F2'!$C$2:$C$13;1) and you just need to put your
new data in the table F2!$C$2:$C$13

I really don't understand how you managed to get an empty data range!
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂
0
Réponse 3 / 8
Le Pingou Posted messages 12375 Registration date   Status Contributeur Last intervention   1 476
 
Hello,
Is sheet 1 and the related graphs in the same workbook?
--

Best regards.
Jean-Pierre
0
Réponse 4 / 8
Raymond PENTIER Posted messages 58550 Registration date   Status Contributeur Last intervention   17 475
 
It's surprising: I was doing the procedure on Excel as I was writing it for you on CCM, and it worked!
There is certainly a step that you didn't follow scrupulously...
Or maybe your version of Excel 2007 is not the same as mine!

So I suggest two things:
1) You try the process again one more time.
2) You send me your file via https://www.cjoint.com/
--
It's nice, retirement! Especially in the Caribbean... :-)
☻ Raymond ♂
0
Michel 4286
 
Here is the link:

https://www.cjoint.com/?lktC0GQQX6

Thank you for your help.
0
Raymond PENTIER Posted messages 58550 Registration date   Status Contributeur Last intervention   17 475 > Michel 4286
 
Could you save a copy of your file in 2003 format and post it again? Thank you.
The 2007 version is consistently compressed and it would need to be unzipped upon arrival!
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂
0
Le Pingou Posted messages 12375 Registration date   Status Contributeur Last intervention   1 476 > Raymond PENTIER Posted messages 58550 Registration date   Status Contributeur Last intervention  
 
Hello Raymond,
Just a little reminder: if you have the 2007 file in *.xlsx on cjoint.com, there are 2 ways to open it:
1.- right-click on the *.zip file and choose Open with / Excel and it's good to go.
2.- change the extension from *.zip to *.xlsx and it works too.
Regarding your process, it's indeed completely correct and it works. The problem only arises with Michel 4286's example..... I think I have found the little issue, the answer will follow, patience.

Best regards.
Jean-Pierre
0
Réponse 5 / 8
Le Pingou Posted messages 12375 Registration date   Status Contributeur Last intervention   1 476
 
Hello Michel 4286,
The problem arises because the system cannot reassign the data range of the chart, which is too complex.
I suggest using a buffer zone that is used as a reference range.
See the details in the attached file: https://www.cjoint.com/?llrVY5RFnQ
Follow Raymond's instructions (post 2 or 8)

--

Best regards.
Jean-Pierre
0
Michel 4286
 
Thank you very much to Pingou and Raymond for your invaluable help.
Thank you very much for the solution you've provided. I will follow your instructions. The idea of the buffer zone is very good.

Have a good evening. And thanks again.
0
Le Pingou Posted messages 12375 Registration date   Status Contributeur Last intervention   1 476 > Michel 4286
 
Thank you Michel 4286, you're welcome.
--

Best regards.
Jean-Pierre
0
Réponse 6 / 8
pinker5 Posted messages 2 Status Membre
 
Hello,

I have the same problem as "Michel4286" and so I tried to follow your approach to update the table but it doesn't work.
Let me explain: I have an Excel file with several sheets and a chart per sheet, and when I copy a sheet using the method specified above, everything copies normally, but when I want to change the dates on the chart, it does not modify the curve, and I no longer have a curve.
I tried to select the data again but the dates on the horizontal axis of the chart remain the same.

If anyone has a solution that would be great!!!

Thanks in advance.
0
Réponse 7 / 8
Le Pingou Posted messages 12375 Registration date   Status Contributeur Last intervention   1 476
 
Hello,
We are in 2014 and maybe the Excel version is different....!
Upload your file at https://www.cjoint.com/ and post the link.

--
Regards.
The Penguin
0
pinker5 Posted messages 2 Status Membre
 
Thank you for your help.
But I just found the solution!
Actually, the problem was that in the "Chart Tools" tab > "Layouts" then "Axes" and finally "Horizontal Axis," you need to go to the end of the dropdown list, which is "More Options for the Primary Horizontal Axis." Then in "Axis Options," the values were set to fixed when they should have been set to automatic for the "Minimum" and "Maximum" values.
By doing this, the new values entered update in the chart.
Thanks for the reply "Le Pingou"!
0
Frederic
 
Hello Le Pingou, the link for the solution of a buffer zone that is used as a reference area is no longer available. Could you please recreate it as I have the same problem as Michel?
Thank you very much,
Frederic
0
Le Pingou Posted messages 12375 Registration date   Status Contributeur Last intervention   1 476
 
Hello Frederic.
Sorry, I don't have a copy of the file.
It is better to create a new post outlining your issue
.
Regards.
The Penguin
0
Réponse 8 / 8
francois
 
Hello,
if you want to reproduce an entire page with a data range and a chart, you need to copy and paste, then in the new sheet, modify the data selection by changing the name of the original sheet to the name of the destination sheet. Make sure to leave the exclamation mark after the sheet name.
0