Named range reverts to normal range in chart

Question

I have encountered strange problem. I have made named range SomeName

    =OFFSET(Source!$B$29:$C$29,1,0,  COUNTIF(Source!$B$30:$B$55, "<>x"),2)

And then I have tried to used in chart as a chart data range:

=Source!SomeName

As far, it worked fine. But when I switched to the sheet Source and went back I have realised that the data range went back to the normal range =Source!$B$30:$C$33. I guess I could fix it with macro (like setting named range to chart everytime when somebody activate the sheet) but there must be some other way. Anyway know how to make it without VBA?

Kresimir L. Kresimir L. · Accepted Answer · 2017-10-12T09:59:05

You cannot set whole chart source data to be named dynamic range. It will always transfer to hardcoded values. what you can do and will work, is to create named range for each series in chart, than add it manually (for more info take a look at this site). this way your chart will be somewhat dynamic. But the best way to get what you want is by using a macro. Something similar to this code...

Sub Test()
    Dim CHARTDATA As Range
    Set CHARTDATA = Range(or your dynamic range)
    ActiveSheet.ChartObjects("Chart 1").Chart.SetSourceData Source:=CHARTDATA
End Sub

Named range reverts to normal range in chart

1 Answers