Excel charts - setting series end dynamically

Question

I've got a spreadsheet with plenty of graphs in it and one sheet with loads of data feeding those graphs.

I've plotted the data on each graph using

=Sheet1!$C5:$C$3000

This basically just plots the values in C5 to C3000 on a graph.

Regularly though I just want to look at a subset of the data i.e. I might just want to look at the first 1000 rows for example. Currently to do this I have to modify the formula in each of my graphs which takes time.

Would you know a way to simplify this? Ideally if I could just have a cell on single sheet that it reads in the row number from and plots all the graphs from C5 to C 'row number' would be best.

Any help would be much appreciated.

Answer 1

OK, I had to do a little more research, here's how to make it work, completely within the spreadsheet (without VBA):

Using A1 as the end of your desired range, and the chart being on the same sheet as the data:

Name the first cell of the data (C5) as a named range, say TESTRANGE.
Created a named range MYDATA as the following formula:

=OFFSET(TESTRANGE, 0, 0, Sheet1!$A$1, 1)

Now, go to the SERIES tab of the chart SOURCE DATA dialog, and change your VALUES statement to:

=Sheet1!MYDATA

Now everytime you change the A1 cell value, it'll change the chart.

Thanks to Robert Mearns for catching the flaws in my previous answer.