Changing Dynamic Graph Data Based On Cell Data

[HomeTek]

Hi all,

Do you know if there is any way (without using VBA) that I can get the graph to change depending on the value entered into cell E2.

A4 will always be the starting cell data for the graph, but the graph will take more days into consideration the higher the value in E2.

Thanks

 

[JoeMo]

Can be done by specifying two named ranges which I call xData and yData (below). Both are specified at the sheet level and are defined as:

xData: =OFFSET($A$4,0,0,$E$3,1)
yData: =OFFSET($B$4,0,0,$E$3,1)

Then select the chart, click on a bar to produce the series formula in the formula bar and manually change the x and y series to xData and yData to produce the result below in the formula bar. Note that you must precede the named with the sheet reference (in red). I've done this on Sheet2.

=SERIES(Sheet2!$B$3,Sheet2!xData,Sheet2!yData,1)

The chart should now adjust automatically when the value in E3 is changed.

 

[HomeTek]

Can be done by specifying two named ranges which I call xData and yData (below). Both are specified at the sheet level and are defined as:

xData: =OFFSET($A$4,0,0,$E$3,1)
yData: =OFFSET($B$4,0,0,$E$3,1)

Then select the chart, click on a bar to produce the series formula in the formula bar and manually change the x and y series to xData and yData to produce the result below in the formula bar. Note that you must precede the named with the sheet reference (in red). I've done this on Sheet2.

=SERIES(Sheet2!$B$3,Sheet2!xData,Sheet2!yData,1)

The chart should now adjust automatically when the value in E3 is changed.

Thanks for your reply.

Sorry for being a bit dim, but where am I specifying the named ranges? Do I enter the formula above in the "Define Name" section of the Formulas tab? I haven't quite got the hang of named ranges.

 

[JoeMo]

Thanks for your reply.

Sorry for being a bit dim, but where am I specifying the named ranges? Do I enter the formula above in the "Define Name" section of the Formulas tab? I haven't quite got the hang of named ranges.

Yes, that's where you enter the xData and yData formulas. And be sure to specify the sheet in the dropdown for Scope that appears in the New Name dialog box.

The SERIES formula changes are made in the formula bar after you have selected the chart and clicked on a bar.

 

[HomeTek]

Yes, that's where you enter the xData and yData formulas. And be sure to specify the sheet in the dropdown for Scope that appears in the New Name dialog box.

The SERIES formula changes are made in the formula bar after you have selected the chart and clicked on a bar.

I seem to be going wrong somewhere. No doubt I'm missing something obvious. A couple of relevant screen prints below.

You can't see the end of the formula above but it's exactly the same as you mentioned above. As is the yData version so I didn't bother screen printing both, just 1 as an example.

 

[JoeMo]

Appears to me that your key offset parameter is in E2 NOT E3. Try changing $E$3 to $E$2 in the xData and yData name definitions.