Weekly Chart With Ability To Change Start Date

I have a simple bike log that lists out each day of the month in rows with a summary row between months. Like this...

January Summary
Jan 1
Jan 2
Jan 3
....
Jan 31
February Summary
Feb 1
Feb 2
Feb 3
....
Feb 28

and so on. Each column has a data field where I enter in miles and distance and so on. I frequently add rows if I do two rides in a day so two rows may start with "January 23rd". I would like to have a chart that shows me my weekly ride summary. But I have some issues:
1. With the field summary I don't know the best way to do a chart that doesn't include the summary (the summary data would throw the chart way off)
2. If I have multiple rides in a day I don't know how to make the week include those days
3. I may want the week to start on Monday and not Sunday. Is it possible to have the user choose and have the chart change automatically?

Lots of stuff here. I hope it makes sense.

Guy

Re: Weekly Chart With Ability To Change Start Date

Quote from Dave Hawley

No time for all your questions, sorry. But to make a Dynamic Named Range to include only 1 week from a starting date of choice, do this.

1) Use any cell to hold a Data Validation List of valid dates and then Name this cell StartDate

2) Create a Dynamic Named Range for the chart date Series and link it to the StartDate. E.g.

=OFFSET(Match(StartDate,$A$2:$A$100,0),0,0,7)

Or, you could try a PivotChart.

Display More

Dave,
Thanks for your reply. I think your solution only shows the sum from one date down seven days. That will work if I am only trying to sum 7 days but there are two problems.

1. I have to do every seven days through the year (weekly chart for entire year)

2. If another day is added to the week will this expand to include that new day?

I may be implementing it wrong so your solution may be the right one with my mistakes.

Guy

Re: Weekly Chart With Ability To Change Start Date

Thanks for your messages. I have avoided using Pivot Tables in the spreadsheet because of the problems when people with Macs try to use them. I may revisit that idea and see if Macs still have difficulties with pivot tables.

Another idea is to make the weeks always start on Monday and just hardcode the data as two separate colums (one for date and one for mileage summary). It is a work-around and not my first choice. I would rather do this with smart coding.

Guy