microsoft excel - How do I create a dynamically-growing graph which ignores empty tail cells?

I have a table on an Excel 2010 Worksheet 'Consolidated Data' where:

• Column A is filled with arbitrary dates sorted by increasing age


• Column F has numeric data relevant to this question


• Row 1 is the header / title row.


• Data begins on Row 2.


• There are no intermediary rows with empty cells.


• Data currently ends on Row 34.

In the same Workbook is Worksheet 'Graphs' which has a chart named Data over Time and the data range of:

Consolidated Data'!$A$2:$A$100,'Consolidated Data'!$F$2:$F$100

Charted data on the graph after Row 34 appears as a straight line at the highest value.

I am trying to get the graph to stop adding the empty data after 'Consolidated Data'!F34, but to also grow in width dynamically if I add new data to 'Consolidated Data'!F35.

I tried to follow the advice from here, but am getting an error when setting the series as the Chart data range.

I tried to create names as follows:

'Consolidated Data'!YVal    =OFFSET('Consolidated Data'!$F$2,0,0,COUNTA('Consolidated Data'!$F:$F)-1,1)
'Consolidated Data'!XVal    =OFFSET('Consolidated Data'!YVal,0,-5)

but when I set the series per the article

=SERIES(,'Consolidated Data'!XVal,'Consolidated Data'!YVal,1)

I get an error.

Of note, this is the first time I'm creating names in Excel. When I click the name box, type a name, hit 'enter', and then enter the data in the formula, the current cell I'm in (happens to be unused Column G on 'Consolidated Data') displays a value.

What am I doing wrong, and how can I achieve the intended goal?