Dynamic chart with end date and count backward

[kh40sth30ry]

Hello,

I have a quick question regarding plotting the interactive chart. So i follow John Peltier instructure to create a rolling chart.
http://peltiertech.com/Excel/Charts/DynamicLast12.html

However, i have to do something more indepth, and i wasn't able to do it using his example. Basically i want to be able to rollback any number of month entered by me, but limit to the earliest point of the data. For example:
My data is from Jan 11 to Jun 11. I then can choose and month during the period and want to display the last 7 months, it will not give me error but just graph whatever data available instead.

Thank you for reading my post.

 

[Steve=True]

In his formula you would need to change the min look up to a cell reference of your choice.

"But if the range is shorter, the OFFSET formula would refer to rows before row 1. The term <TT>MIN(chtLen,COUNTA(Sheet1!$A:$A)-1)</TT> takes the actual length of the range if it's less than <TT>chtLen</TT>:

=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0,-MIN(chtLen,COUNTA(Sheet1!$A:$A)-1),1)"</PRE>

=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0,[CHANGE ThIS WITH YOUR MONTHS VARIALBE CHOICE CELL REFERNCE]-1),1)</PRE></PRE>​