Dynamic Chart Updating

[troy_lee]

Column A on a datasheet is the week number. This is the chart's X axis. Column C is the value that gets plotted on the Y axis.

I want to make the chart dynamic so that every week when I add a new row, the chart's data range not only expands to include the new row but also readjusts the first row of the range to be one more than the previous week. In this manner I would always have only 52 weeks graphed on the chart.

Thanks in advance for the help.

Troy

 

[gardnertoo]

Named Ranges are the way to go here. Use the following menu path to get there:

Once you are there, click in the box at the top (where you see "Staff_Name" in my example) and type a name, maybe "Weeks". Then click in the "Refers to" box and enter this formula:
=OFFSET(Sheet1!$A$1,COUNT(Sheet1!$A:$A),0,-52,1)
then click "Add". This will add the named range and activate it. If you now click in the "Refers to" box again, you should get the "dancing dots" box around the last 52 week numbers on the page. If not, the formula needs to be adjusted. Do the same for a new named range, maybe call it "data", defined by this formula:

=OFFSET(Sheet1!$C$1,COUNT(Sheet1!$C:$C),0,-52,1)

You can then replace the references in your Source Data with these names, and the data will march down the sheet as your data grows. So instead of X Values and Y Values being defined as:

=Sheet1!$A$2:$A$53 and =Sheet1!$C$2:$C$53

you would change it to:

=Book1!Weeks and =Book1!data