Function that will create a range based on a start date and end date

Hi all, I have been trying to create a "line with markers" chart, having the X axis be dates in increments of 7 days, ie; 5/5/13, 5/12/13, 5/19/13, 5/26/13, 6/2/13...etc. I can manually or drag a cell to auto fill the range in the input sheet columns no problem. What I want to have happen, is for my range to reference a start date "5/5/13" which would be located in a cell of my choosing, lets say =('sheet1'!A1) and auto populate the range up to my end date which will be another reference cell =('sheet1'D1). So the chart would start at the date referenced in cell A1, and each marker point would be +7 days as shown in my example above, and stop at my end date referenced from cell D1.

I hope this makes sense and someone can help. I have been able to find so many great tips on this forum and others in the past, I have learned how to create macros, write certain VBA's and much more. But this problem is so specific I havent been able to find anything helpful.

Thanks in advance!

Chris

Use this formula in the first cell of your range:

=IF(Sheet1!$A$1+7*(ROWS($1:1)-1)<=Sheet1!$D$1,Sheet1!$A$1+7*(ROWS($1:1)-1),"")

format the cell as a date in the style you prefer, then copy down as far as you like.

If you want to copy the formula across your sheet, then use this instead in your first cell:

=IF(Sheet1!$A$1+7*(COLUMNS($A:A)-1)<=Sheet1!$D$1,Sheet1!$A$1+7*(COLUMNS($A:A)-1),"")

then copy across.

Hope this helps.

Pete

Wow, thanks Pete, that works perfect. One more Question... Is there a way to make my chart only reflect the range of my dates? Right now, I can manually do this by selecting and "hide" the columns that are blank, so they dont add to the range length of my chart. So, if my dates are 10 columns worth of dates at 7 day incriments, b-L, but I have 20 columns in my chart input sheet, it will fill out the chart half way. See pic.