Solved

How to copy data with corresponding chart so the new chart references the new data?

Posted on 2016-11-15
6
64 Views
Last Modified: 2016-11-18
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!
0
Comment
Question by:psteff
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:Karen Falandays
ID: 41889690
Hi Psteff, how about conditional formatting>Data Bars or sparklines? I have attached a sample
kfalandays
0
 
LVL 2

Author Comment

by:psteff
ID: 41889834
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.
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 41891107
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.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 33

Expert Comment

by:Rob Henson
ID: 41891671
There is no workbook in your question or your comment.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41891678
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.
0
 
LVL 2

Author Comment

by:psteff
ID: 41893370
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
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question