Dynamic chart not keeping range formula

[Dandas]

Hi guys.

I haven't posted anything here for years so sorry if this has come up before.

I have a report in excel used in a call centre that tracks the number of calls an agent makes. Team Leaders want to see performance by month on a chart.

I have a chart that has an offset formula to change the range based on how many days there are in the month. eg if the month they want to see is Feb then it will only have 28 days of data on the chart. If it is Jul it will increase the days to 31 days. It also changes depending on how many people are in the team. If only 6 people on the team there are 6 columns of data. If there are 8 people the range will increase by 2 people.

The result of the Offset formula changes based on two ranges that update which are the days in the month and the number of agents.

The problem I am having is when I enter the Offset formula the chart updates perfectly. But then when ranges change that make up the offset formula the chart does not update.

If I go into the formula again the chart just has the static range, not the Offset formula. If I re-enter the formula after the ranges in the offset formula have changed the chart updates perfectly again. But I have to re-enter the formula.

It just seems to not keep the formula in the chart.

Does anyone have any ideas? This is what I am entering into the formula bar.

=Offset(ChartDataStart,0,0,DaysCount,AgentsCount)

I am stumped.

Thanks !!

 

[GlennUK]

If sounds like you are putting the Offset formula inside the chart series definition, whereas I would recommend that you put it inside a defined name, and refer to that in your chart series definition.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Dandas]

Hi Glen.

Thanks for that.

I actually tried that but found that by putting the formula in a named cell it tries to return the result of the formula. Since the range takes up many cells it comes back with

#VALUE!

I also got some code off google to craete a function that displays the formula inside a cell, then pointed the chart to the actual formula and it still does not weork.

Anyone else have any ideas?

Thanks

 

[Dandas]

Also, I have tried to just add a name in Insert -> Names -> Define and added the formula to the name but it does not stay there. Every time I change the days or number of agents the graph does not update and when I go into the graph the formula does not refer to the name, it just has the current range.

Thanks

 

[Dandas]

For anyone interested I fixed it by adding the following code in combination with my Formula in a Named Range which is "ChartFormula".


Sub UpdateChartSourceData()

With ActiveSheet
.ChartObjects(1).Chart.SetSourceData _
Source:=.Range("ChartFormula"), _
PlotBy:=xlColumns
End With


End Sub