Dynamic chart always looking 3 months back and 6 months forward

[BackseatBoss]

Hey guys,

I have been working on this one problem the entire day and I am completely stuck at the moment. I have tried solving it and asked my coworkers, but they were also out of ideas. I know I am not anywhere close to being an expert Excel user, which is why I was told that you guys on this board might have the answer to the question I have.

OK, so about the problem. I was asked to make an Excel file which would have a Pivot table and chart that would always show the current month, 3 months back and 6 months forward. Basically I want to make such a sheet that the data would be dynamically renewing and the chart would update itself whenever you would roll onto the next month. Let's say this month is April, the chart should have January-February-March (the 3 previous months), April (current month), and May-June-July-August-September-October (the forecast of the next 6 months). Then once May rolls around, the chart should start with February-March-April (last 3 months) and so on... I hope you guys can understand the gist.

In the end I want that sheet to be able to just pull the newest data from the internet, the charts to update through the code/Excel formula that is written and the people that have access to the sheet would not have to manually reshuffle the months around. Of course the pulling of data part I am not having problems with, it's just the dynamically updating Pivot chart. Does anyone know what a possible solution could be?

Again, I am kind of a novice in this, so I really hope that some of you could help me out, since I know that there are tons of experts on this board

 

[onlyadrafter]

Hello,

This probably won'e answer your question directly, but it may point you in the right direction.

As you haven't mentioned anything about sheet layout I have assumed everything.

In A1:A12 I have Jan, Feb, ... Dec

In D1 I have =today()

In E1 I have =IF(MONTH($D$1)+ROW()-1<=3,MONTH($D$1)+9,MONTH($D$1)-3)

In F1 I have =INDEX($A$1:$A$12,E1,0)

Copy E; and F1 down as far as row 9.

E1: E12 will have 12, 1, 2, 3, 4, 5, 6, 7, 8

F1:F12 will have dec, jan, feb, mar, apr, may, jun, jul, aug.

You will need to work out where you go from here to show the correct data relevant for your pivot.

You should then be able to reference the same range for your pivot chart.