Absolute References in charts.

[LightWrath]

Hi there,
I'm sure this is something I'm doing wrong with the correct source but not sure what exactly I'm doing incorrectly.

I have a chart that I'm adding data to each week.
The Chart references from A2:B3000. When adding a week of data it changes to A7 and requires re-editing.
Looking on the following post http://www.mrexcel.com/forum/excel-questions/415495-absolute-referencing-chart-source-data.html
I can use the 'Indirect' function. But I'm generally getting errors in using this.
I'm using Excel 2013 and creating a line chart. When going to select data source (Select Data), and using the Chart Data Range field. Entering the function =INDIRECT("Sheet1!A2:B3000"), it returns the error' That function isn't valid'.
Is anyone able to assist?
Thanks.
LightWrath

 

[pgc01]

Hi
Welcome to the board

You cannot use the Indirect() directly in the chart series. You must use names.

For ex., define the names in Sheet1

Name: XValues
Refers to: =INDIRECT("Sheet1!$A$2:$A$3000")

Name: YValues
Refers to: =INDIRECT("Sheet1!$B$2:$B$3000")

Now add a chart, and in Chart Tools->Design->Select Data add a new series with

YValues: =Sheet1!Yvalues
XValues: =Sheet1!Xvalues

Now insert some rows at row 2. Check the chart. You'll see that although you have inserted the rows the series still refers to the same range in rows 2:3000.

 

[LightWrath]

Ohhhh....

Thanks for your reply, I had no idea about the names feature actually so this is why I didn't catch on when it was originally stated about name referencing in the post I linked.
So thank you for clearing this up, works great now and shall save me a lot of time in the future.

LightWrath

 

[pgc01]

You're welcome. Thanks for the feedback.