Dynamic Chart Axis - OFFSET function?

[Caleeco]

Hello,

I'm struggling with an Excel chart problem, which Im hoping the community can help me solve!

I have a chart which I wish to make dynamic eg. if the current month is November, the chart axis should display data for Sept, Oct, Nov, Dec, Jan (2 months before & 2 months ahead).

1. The Data is on sheet called "Summary Sheet"
2. Cells B3:B26 contain dates, 01/01/2016, 01/02/2016, 01/03/2016.... all the way to 01/12/2017

I have the following formula to work out which month we are in, and what the matching date should be:

=DATE(YEAR(TODAY()),MONTH(TODAY()), 1)

However, How can I get the chart to use the formula to pick out the correct range from cells B3:B26?

Trying a few things with names ranges, and the OFFSET function.. but not luck yet!

Any help is appreciated (sorry I cannot currently post/upload a picture of my current worksheet)
Thanks
Caleeco

 

[abhi1687]

you can use offset as below:

=OFFSET(INDEX($B$2:$B$26,MATCH(EOMONTH(TODAY(),-3)+1,$B$2:$B$26,0)),,,5)

or you can use index as below:

=INDEX($B$2:$B$26,MATCH(EOMONTH(TODAY(),-3)+1,$B$2:$B$26,0)):INDEX($B$2:$B$26,MATCH(EOMONTH(TODAY(),-3)+1,$B$2:$B$26,0)+4)