Copy chart and change data range

Assuming this is a one-off and you want an easy manual process,.

If your data is organized so that the data is on separate sheets, but you want to reference the same cells on those sheets for the charts, here is how you can do it.

Copy the chart and paste it. In a chart where you want to change the data source:
- Right click on the chart and select Select Data. This will list the series in the chart. (See picture).
- Click on a series and select edit. This will tell you where the data range for that series is pointing.

In this case it is pointing to Pareto!$E$4:$E$13. Replace Pareto with the sheet name containing the data. Use the single quote if the sheet name has spaces. Better still, name the sheets a, b, c, etc. then you can simply type a!, b!, c! ,etc. When you change the sheet names back the charts will pick up the new sheet name.

If you have a lot of charts and a lot of series this can be tedious and you may want an automated solution. In that case, attach a sample workbook.

Greg, what you are describing, I call the "dashboard approach." Depending on how the data are laid out, it can also be accomplished using named dynamic ranges.

I thought this sounded familiar. I snooped around my archives and found this.

Please Login or Register  to view this content.

This code changes all the charts on the active sheet to reference the ranges on that sheet. TSheet is the "Template Sheet" the sheet from which the chart was copied.

It could be made a bit more general by enclosing the chart object loop in a sheet loop.

The explanation is rather long and this tiny window isn't the best word processor, so the explanation is in the word document.

Copied from the Word document:

Since this is a rather long explanation, I decided to put it in a word document rather than try to post it.

This dashboard is accomplished using named dynamic ranges defined by the offset command and using these names in the charts.

A more generic explanation is given in the following two wikis:
http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges
http://www.utteraccess.com/wiki/Dynamic_Charting

I assumed that each data set has an identifying number in column A and that the number matches up with the first row of the data set. I also assumed that there is exactly one blank row between data sets. I had to “cheat” and added an “End” data set in Cell A199, so I could find the end of the last data set.

There is an old Beatles’ song: I Get by With the Help of my Friends. In this case, I get by with the help of an entire helper sheet.

Columns A:D are used to find the boundaries of the data ranges. I listed the sets 1-12 (plus End) in column A and use this range for data validation in the drop-down list in Cell C1 on the BT sheet.

Column B uses MATCH to find the start of each data set. Column C assumes that the end of each data set is exactly two rows above the start of the previous set. The difference (Column D) is the number of rows in the data set.

When you make a section in Cell C1 on the data sheet, it is reflected in cell G1 on the helper sheet. This value is used by VLOOKUP (against columns A:D) to look up the start row (offset – Cell G2) and number or rows in the data set.

The baseline row is row 4 on sheet BT, so this amount is subtracted from the sheet position of the offset.

I decided that rather than shift the range on the GT sheet, I would mirror it on the helper sheet and use that range to plot the chart. Columns J:N do this. This table is set up to plot a maximum of 25 rows in a data set, but you can extend the table to plot more.

I will use data set 10 as an example.

Cell J2 is the number of rows to offset from the base cell. Column K is the “X-axis” and columns L:N are the three series.

Cell J2 is the offset for the beginning of the range. In the example, the data we want starts 154 rows below the base row (Cell B4 on the BT sheet). Each row below that corresponds to one row more on the BT sheet. This information is used to look up the data for columns B (Verfahren), I, J and K which are 2016, 2017 and 2016+2017.

We now have more data than we need to plot. It will be “masked” with named dynamic ranges.
Plot_2016 =OFFSET(Plot_Verfahren,0,1)
Plot_2016_2017 =OFFSET(Plot_Verfahren,0,3)
Plot_2017 =OFFSET(Plot_Verfahren,0,2)
Plot_Verfahren​=OFFSET('Helper Sheet'!$K$2,0,0,'Helper Sheet'!$G$3,1)

Plot_Verfahren is the most important one. Once you have a basic range established, you can define other ranges from it.

The offset formula has 5 parameters:
Starting Cell (K2)
Number of rows to go down (0)
Number of columns to go right (0)
Number rows to return (whatever is in Cell G3)
Number of columns to return (1)

This pares the data down to the first 11 rows in the “table.”

These names are then used to build the chart (see references).

Thanks Ali. I find that if I copy / paste from a word document and then go back and edit my post, the text disappears.