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

<p>I've plotted the data on each graph using </p>

<pre><code>=Sheet1!$C5:$C$3000
</code></pre>

<p>This basically just plots the values in C5 to C3000 on a graph.</p>

<p>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.</p>

<p>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.</p>

<p>Any help would be much appreciated.</p>

You can set the range for a chart dynamically in Excel. You can use something like the following VBA code to do it:
```
```
Private Sub Worksheet_Change(ByVal Target as Range)
    Select Case Target 
    Case Cells(14, 2)
        Sheet1.ChartObjects(1).Chart.SetSourceData Range("$C5:$C$" & Cells(14,2))
    ...
    End Select
End Sub
```
```
In this case, the cell containing the number of the last row to include is B14 (remember row first when referring to the Cells object). You could also use a variable instead of the Cells reference if you wanted to do this entirely in code. (This works in both 2007 and 2003.) You can assign this procedure to a button and click it to refresh your chart once you update the cell containing the last row.
However, this may not be precisely what you want to do ... I am not aware of a way to use a formula directly within a chart to specify source data.
Edit:
And as PConroy points out in a comment, you could put this code in the Change event for that worksheet, so that neither a button nor a key combination is necessary to run the code. You can also add code so that it updates each chart only when the matching cell is edited.
I've updated the example above to reflect this.