Solved

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

Posted on 2016-11-15
6
42 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
  • 3
  • 2
6 Comments
 
LVL 17

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 31

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 31

Expert Comment

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

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now