How do I copy Chart offset functionality to any number of different worksheets

[SiChambo]

Hello everyone

I have been scratching my head over this most of the day.

I have developed a great project summary report which provides the user with several useful charts, auto filters, etc from raw data.

My challenge is this, I have created a chart that offsets its data using the Offset formula and name manager, there could be any number of rows of data and I only want to see actual populated rows. which I have done, and this works exactly how I want it to.... However when I copy and paste the whole worksheet, it seems to lose the offset functionality and only returns the rows used from the original copied sheet for example Original chart has 15 rows of data, copy has either 6 rows or 24 rows and it shows either 6 rows of data and 9 blank rows or the first 15 rows not the full 24.

I am quite experienced user of excel and formulas but relatively new to Name Manager and Dynamic charts.

When I add the offset reference into the Name manager I have tried to scope both Workbook and template sheet but neither seem to work

If anyone can advise me where I am going wrong I would really appreciate.

Many Thanks in advance

Si

 

[NARAYANK991]

Hi ,

I think there is no solution to this problem ; probably you can implement the whole copy + paste operation in VBA so that the charts can be copied correctly.

What I have seen is that if the chart is based on a dynamic range , when the sheet is copied , the named range is copied correctly , but the chart itself loses its series definition in terms of the dynamic named range ; instead the series is a static range using absolute addressing.

A worksheet which has the following chart series definition :

=SERIES("Com",COT_Test.xlsx!Date,COT_Test.xlsx!NetCom,1)

when copied , the new sheet which is a copy , has the following chart series definition :

=SERIES("Com",'MINDEX (2)'!$A$4:$A$23,'MINDEX (2)'!$I$4:$I$23,1)

where the dynamic named ranges have been replaced by their equivalent static absolute addressing references.

The workaround must be to use VBA and restore the definition after the copy.

Narayan

 

[SiChambo]

Hi ,

I think there is no solution to this problem ; probably you can implement the whole copy + paste operation in VBA so that the charts can be copied correctly.

What I have seen is that if the chart is based on a dynamic range , when the sheet is copied , the named range is copied correctly , but the chart itself loses its series definition in terms of the dynamic named range ; instead the series is a static range using absolute addressing.

A worksheet which has the following chart series definition :

=SERIES("Com",COT_Test.xlsx!Date,COT_Test.xlsx!NetCom,1)

when copied , the new sheet which is a copy , has the following chart series definition :

=SERIES("Com",'MINDEX (2)'!$A$4:$A$23,'MINDEX (2)'!$I$4:$I$23,1)

where the dynamic named ranges have been replaced by their equivalent static absolute addressing references.

The workaround must be to use VBA and restore the definition after the copy.

Narayan

Narayan Hi

Many thanks for the speedy reply I kind of figured that would be the case.
As mentioned earlier I am relatively new to offset and dynamic charts, how would I go about using VBA to capture this action?

I understand VBA but it is only what I have picked up over a number of years and not trained in the fine art so any pointers would be greatly appreciated.

Many thanks Si