Forum Discussion
growing table
Maxwellq Why not simply convert your data into a structured table (Ctrl-T) and run your graph off that table. Such tables expand automatically and anything referring to the table as a whole or a column that is part of the table will expand with it as demonstrated in the attached file. Just add a few lines of dates and weights at the bottom and see what happens.
Excel allows you to define dynamic named ranges - see for example Dynamic Named Range.
In your example, you could create a range named XValues defined as
=OFFSET(Sheet1!$A$1,2,0,COUNTA(Sheet1!$A:$A)-2,1)
assuming that A1 and A2 are filled, but the dates begin in A3. Sheet1 is the name of the sheet with data.
Similarly, create a range named YValues defined as
=OFFSET(Sheet1!$B$1,2,0,COUNTA(Sheet1!$B:$B)-2,1)
You can now specify =Sheet1!XValues as the range for the category axis values of your chart, and =Sheet1!YValues as the range for the series.
The chart will dynamically adapt itself as you add data.
And you can use =MAX(YValues) to return the maximum weight.
- This is very useful to know.
Thank you very much.
