Dynamically Change Chart Data Range
 

Hi All,

I have a chart that I want to be able to update automatically. I used an ADDRESS function to get the reference of the cell that I want. And if I use INDIRECT I can get calculations to happy with this cell reference so I know that it is correct to there.
=ADDRESS(MATCH($C$2,$B$1:$B$10000,0),2,1,1)
And if I reference the solution to the above using this formula,
=SUM($B$6:INDIRECT('event lookup (2)'!$D$2,TRUE))
I get the correct answer.

There are several series on this graph and I don't want to have to manually update all of them. I'm looking for a way within the Series Formula [x-values], so have this value linked in somehow.

The table: I have several columns of data and I want the X-values only to include up to a certain date, say 3/1/2012. So I want my x-values to be $B$6:$B$80, but next time I update, I will want it to be $B$6:$B$85 cued off of the date in a cell at the top of the table.

How can I use this cell reference in my X values to make the graph dynamic?

Thanks,
Chelsea

Dynamically Change Chart Data Range
 

On Wednesday, May 2, 2012 3:08:28 PM UTC-5, chelseamd89 wrote:
Hi All,

I have a chart that I want to be able to update automatically. I used
an ADDRESS function to get the reference of the cell that I want. And
if I use INDIRECT I can get calculations to happy with this cell
reference so I know that it is correct to there.
=ADDRESS(MATCH($C$2,$B$1:$B$10000,0),2,1,1)
And if I reference the solution to the above using this formula,
=SUM($B$6:INDIRECT('event lookup (2)'!$D$2,TRUE))
I get the correct answer.

There are several series on this graph and I don't want to have to
manually update all of them. I'm looking for a way within the Series
Formula [x-values], so have this value linked in somehow.

The table: I have several columns of data and I want the X-values only
to include up to a certain date, say 3/1/2012. So I want my x-values to
be $B$6:$B$80, but next time I update, I will want it to be $B$6:$B$85
cued off of the date in a cell at the top of the table.

How can I use this cell reference in my X values to make the graph
dynamic?

Thanks,
Chelsea




--
chelseamd89

You can do this with a formula using offset from the bottom