Flexible Chart Range - Rolling x-month period

[David Evans]

I am stuck on this ...

I have a range of dates and associated variables e.g. Sales, Costs and Profit

If I select a date within a range, named "Rept_Date" - I want to chart from that date to another date derived from EOMONTH(Rept_Date,Rept_Period) - is this the right technique?

I'm able to get the range to format itself using Conditional Formatting equation, but I keep striking out on defining the same range to plot - I hope I haven't confused you as much as I've confused myself on this .....

Any insight would be greatly appreciated

 

[Hui]

I would use 4 named formula as:
Date_Rng: =OFFSET(Sheet1!$A$1,,MATCH(Sheet1!$B$15,Sheet1!$B$1:$BL$1,0),1,-Rept_Period)
Sales: =OFFSET(Date_Rng,1,)
Costs: =OFFSET(Date_Rng,2,)
Profit: =OFFSET(Date_Rng,3,)

see attached file:


As a comment I prefer to work with dates as 1st of the month rather than the last day of the month as you have done, I just find it less confusing

 

[David Evans]

Thanks Hui - I agree about the 1st of the month - it's a lot easier to define for starters as it's always 1 - the project I'm working on currently uses the last day, but that will get changed during the process improvement!