• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 97
  • Last Modified:

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!
0
psteff
Asked:
psteff
  • 3
  • 2
1 Solution
 
Karen FalandaysTraining SpecialistCommented:
Hi Psteff, how about conditional formatting>Data Bars or sparklines? I have attached a sample
kfalandays
0
 
psteffAuthor Commented:
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
 
Rob HensonFinance AnalystCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Rob HensonFinance AnalystCommented:
There is no workbook in your question or your comment.
0
 
Rob HensonFinance AnalystCommented:
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
 
psteffAuthor Commented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now