Fixing a chart data range

[Rob65]

Hi All,

I have what is probably a simple problem but can't see a quick fix.

I have a number of charts that feed of data in other tabs. The top row of the data tabs represent dates. Periodically I need to update these sheets and have some VBA code that does this. When the update runs, part of the process loops through the date headers and inserts columns where data values are missing from the series sequence.

The problem occurs because the inserted columns become an explicit part of the charts' data ranges. i.e. if the data range covered columns A through E, inserting a between B and C forces the data range to become A:F. I guess I could go into the charts data range parameters and redefine them, but I wondered if the is an easy way fix the chart's data range in an absolute sense.

Hope this makes sense. Thanks.

Rob

 

[oldbrewer]

when you insert the new column between B and C do you want the chart to be based on the new A:E ?

 

[Rob65]

Hi Yes. I want to keep the data range absolute, regardless of what happens on the data tab. I have made a work around by accessing the chart data range parameters, but was curious to know if there was an easy way to bind the chart to an absolute range.

 

[oldbrewer]

date

01/04/2017

03/04/2017

04/04/2017

05/04/2017

07/04/2017

09/04/2017

original data

01/04/2017

03/04/2017

05/04/2017

07/04/2017

09/04/2017

value

7

4

11

8

6

9

lower dates generated with offset

01/04/2017

03/04/2017

05/04/2017

07/04/2017

09/04/2017

before new column with 4/4/17 added

01/04/2017

03/04/2017

04/04/2017

05/04/2017

07/04/2017

7

4

11

8

6

now the table above copes with the new column insertion

formula giving 1/4/17

=OFFSET($A$1,0,1)

offset from $A$1 is not affected by the column insertion

<colgroup><col><col span="3"><col><col><col><col><col><col><col><col><col><col span="7"></colgroup><tbody>
</tbody>

 

[Rob65]

Thanks for your reply. The simplest solutions are always the best - I think that should work fine. Much appreciated.
Kind regards,
Rob

 

[oldbrewer]

you are welcome
kind regards
Bob