Index / Match / sumproduct / date calculation

[jaredn]

I am over my head on this one and could use some help. I am on excel 2007 and trying to automate some tasks that I have been doing by hand.

I have made one workbook with three sheets. The first is a dashboard to summarize the data, the second is a data tab for ships and sales, and the third a data tab for current warehouse inventory.

Problem #1
I would like to take the data from the second sheet and group it by date ranges on the first sheet. The data on the second sheet is ships and sales by day by store. I would like to group by 7 day intervals. 0-7 days, 8-14, 15-21. Since the report is updated throughout the week and always need 7 days weeknum didnt work.

Problem #2
I would like to populate the data on the first page, based on a selectable list(B4), by category. I have done this with vlookups, but that was before the date calculations were needed.

I'm not sure if this will work, but here is a screen shot of the dashboard:

http://www.flickr.com/photos/60308177@N02/5502799329/#/photos/60308177@N02/5502799329/lightbox/

screen shot of data page:
http://www.flickr.com/photos/60308177@N02/5502799337/#/photos/60308177@N02/5502799337/lightbox/

Any help is greatly appreciated !!!