Create a chart with dynamic series arguments

[DAOJ]

I’d like to make my charts dynamic in that the data will move and I do not want to have to manually update the Series() function with each change. For instance I intend to enter a number in a cell which will update all the arguments in the series formula. Eg enter ‘5’ and the series formula will include all the details for this series.
I have tried the Offset formula in a named range however could not see how to make this dynamic in that the offset formula is explicitly entered into the name manager rather than the named range linking to a cell which contains the offset formula. At least that’s what I took from other forum responses. Nb it’s not the size of the data range that I want to make dynamic, rather all the arguments in the series function that I’d like to parameterise so users have control. Hope this is clear? Any help or other suggestions appreciated.

 

[Hui]

Daoj

Firstly, Welcome to the Chandoo.org Forums

Start by having a read of this post: https://chandoo.org/wp/dynamic-chart-data-series/

The series from the chart are linked to a Named Formula and hence change as the data change

Try that and if you have problems post a sample file back here?

 

[Peter Bartholomew]

The advice you have received elsewhere is perfectly correct. A named formula y that refers to
= OFFSET( x, 0, k )
will pick up different ranges for y as k is changed.
The chart engine is picky though and requires the name to include the book or sheet as appropriate to the scope of the name e.g.
=SERIES("Offset Chart",Book1!x,Book1!y,1)