update chart source data range based on change in # of used rows

[Gingertrees]

There are a lot of posts out here about this, but none that quite answered my question.
I have a worksheet that gets a row added to it daily with the date and other data. There is also a chart based on this sheet; currently I'm manually changing it's source data daily to reflect the new row. There's got to be a way to do that with VBA, right?

Sample data:
Date Boy Cat Dog Elf Fnogph Gnogph Hot
11/9 281 540 32 353 3422 181 3241
11/10 220 336 10 234 3758 191 3567
11/11 245 210 7 268 3968 198 3770
11/12 835 502 45 599 4470 243 4227
11/15 1235 606 22 1223 5076 265 4811
11/16 1323 398 75 2140 5474 340 5134
11/17 2302 567 88 3826 6041 428 5613

Currently, the chart source data is
='Sheet1'!$A$1:$E$12,'Sheet1'!$H$1:$H$12

and then tomorrow it'll be
='Sheet1'!$A$1:$E$13,'Sheet1'!$H$1:$H$13
and Friday it'll be
='Sheet1'!$A$1:$E$14,'Sheet1'!$H$1:$H$14
(etc)

NOTE: Cannot use dynamic named range, as some users here still have Office 2000

 

[VoG]

Dynamic range names work in Excel 2000. See http://peltiertech.com/WordPress/dynamic-charts/

 

[Gingertrees]

Problems. 1)my dynamically defined names neither show up when I highlight the existing data, nor in the list of names, and 2)Maybe I'm trying to do the wrong type of chart, I don't know, but I cannot get this to work in general if I use more than 1 dynamic range at a time. Trying to use a Scatter plot, which is what I thought I was supposed to use for multi-graphs that share the same x-values (dates).

For example, I saved column A as ChtDateVals using the following defined name:
=OFFSET($A$1,1,0,COUNT($A:$A),1)
But when I try to reference it, why is the Y-values area always populated with ={1} ? And replacing the {1} with anything throws an error.

I swear I used to be good at graphs - I don't know what happened... :'-(

 

[Gingertrees]

OK, I did eventually solve it. In case anyone's interested, here's how:
What I described works with Line or Column charts. Scatter = no. Oops.

If you have pre-2007 Excel, this is easy, as it follows the link VoG posted using the chart wizard.
If you're using 2010 like me (in compatibility mode for the users at my co. that still have 2000):
Define a separate named range for each series you want charted - not necessary for a fixed range, but totally so for dynamic (apparently). So I workbook-wide defined columns of "chtboy" "chtcat" "chtdog" "chtelf" in addition to "chtdates" (chtdates = the horizontal values).

Ignore the "chart data range" box entirely. Add series as needed. I listed them like this:
='Sheet1.xls'!chtboy
and now that it's saved, Excel lists them like this:
='FILENAME.xls'!chtboy
(that was something that tripped me up - listing the sheet name and using the apostrophes. No one mentions that!!!)

Use the same naming convention on the y-vals. I hope someone reads this someday and it helps them with the frustration that is creating dynamic charts in 2010.