How to copy data with corresponding chart so the new chart references the new data?
We had a survey with a number of questions. I would like to be able to copy the chart down the number of times for the number of questions and have each new chart updated to the new data.
For instance, I already pull the data in A2 – H6 via formula, so I can easily copy the rows down to get the new data. However, how would one have a reference to this ranges of cells in the chart to the right that as you copy the rows down, the chart range would automatically change to the data in the new rows?
For example, I would love to be able to copy rows 2-6 with the chart referencing data in A2 – H6 into rows 7-11 and have the second chart reference automatically change to the data in A7 – H11.
I don’t want to have to go into each chart and change the range manually each time for all the charts – but, I have the distinct impression that I may have to. BTW - no VBA please. I don't think this would be worth the time and effort of VBA.
Any help would be appreciated. Thanks much! Great forum!
For instance, I already pull the data in A2 – H6 via formula, so I can easily copy the rows down to get the new data. However, how would one have a reference to this ranges of cells in the chart to the right that as you copy the rows down, the chart range would automatically change to the data in the new rows?
For example, I would love to be able to copy rows 2-6 with the chart referencing data in A2 – H6 into rows 7-11 and have the second chart reference automatically change to the data in A7 – H11.
I don’t want to have to go into each chart and change the range manually each time for all the charts – but, I have the distinct impression that I may have to. BTW - no VBA please. I don't think this would be worth the time and effort of VBA.
Any help would be appreciated. Thanks much! Great forum!
ASKER
kfalandays,
Thanks for the great thought! In different situations, your options would be a good choice - but in this case, they really don't give the visual effect that we are looking for. We really are looking for the same type of chart that I have presented.
Thanks for the great thought! In different situations, your options would be a good choice - but in this case, they really don't give the visual effect that we are looking for. We really are looking for the same type of chart that I have presented.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
There is no workbook in your question or your comment.
To expand further on earlier comment:
1) Right click on tab of sheet containing chart and data and use Move or Copy option in menu to create a new sheet in a different workbook. Save both workbooks, I will refer to them as Working File and Temp File
2) In Temp File make amendments to data as required and save. Then Cut and Paste chart and Data into Working File
3) Close Temp File without saving and then re-open, there should now be a copy of amended chart and data in both files. Check data source of graph in Working File, it should not be linked to Temp File. If Link exists you should be able to use Edit Links window to change data source to correct file.
4) Repeat steps 2 & 3 as required.
1) Right click on tab of sheet containing chart and data and use Move or Copy option in menu to create a new sheet in a different workbook. Save both workbooks, I will refer to them as Working File and Temp File
2) In Temp File make amendments to data as required and save. Then Cut and Paste chart and Data into Working File
3) Close Temp File without saving and then re-open, there should now be a copy of amended chart and data in both files. Check data source of graph in Working File, it should not be linked to Temp File. If Link exists you should be able to use Edit Links window to change data source to correct file.
4) Repeat steps 2 & 3 as required.
ASKER
Rob,
Re: Create a copy of the whole sheet with one chart and then Cut and Paste the chart and data from the new sheet back to the required sheet.
Interesting “workaround”. Still fairly tedious, but with many questions, the advantage starts to multiply.
The first time around, I copy the whole worksheet (ws) with one question over to a new ws, as you said, I cut and paste to the original worksheet. I have two questions done at this point. I then copy the whole ws with the two questions to a new ws. Again, I cut and paste back to the original. I now have four done. The next time 8, the time after that 16, etc.
So, if there is a large number of questions, due to the fact that this method’s effect starts multiplying, it would be quicker than going into each chart individually. With less questions, it is simpler just to go into each chart.
This is not quite what I was looking for, but helpful with the larger number of questions. As I originally thought, it doesn’t appear that Microsoft at this point allows a method to copy the rows down in the same worksheet and have the references in the charts dynamically change reference to the new rows. (too bad).
Thanks Rob for your input!!
EE-Sample.xlsx
Re: Create a copy of the whole sheet with one chart and then Cut and Paste the chart and data from the new sheet back to the required sheet.
Interesting “workaround”. Still fairly tedious, but with many questions, the advantage starts to multiply.
The first time around, I copy the whole worksheet (ws) with one question over to a new ws, as you said, I cut and paste to the original worksheet. I have two questions done at this point. I then copy the whole ws with the two questions to a new ws. Again, I cut and paste back to the original. I now have four done. The next time 8, the time after that 16, etc.
So, if there is a large number of questions, due to the fact that this method’s effect starts multiplying, it would be quicker than going into each chart individually. With less questions, it is simpler just to go into each chart.
This is not quite what I was looking for, but helpful with the larger number of questions. As I originally thought, it doesn’t appear that Microsoft at this point allows a method to copy the rows down in the same worksheet and have the references in the charts dynamically change reference to the new rows. (too bad).
Thanks Rob for your input!!
EE-Sample.xlsx
kfalandays