6 month data on a graph

[Goose0310]

Hi! I have a VBA question.

I have a bar graph showing the the number of sales every month for the last 5 months.
For example: 1000 in Oct, 1200 in Nov, 1300 in Dec, 950 in Jan, and 800 in Feb

The Months are in One row (Row A), the number of sales are in another row (Row B).

Next month, I want to show the number of sales for (Nov, Dec, Jan, Feb, March) on the graph. The month after, it would shift again (Dec, Jan, Feb, March, April) and so on

And then keep on repeating that as months go by (looping)

How would I code that loop on VBA?
Or is there an easier way?

Thanks for the help!

 

[JustynaMK]

Hi,

I think it might be easier if you use Named Ranges.

For example, for Oct'18-Mar'19, your current Bar Chart would use two named ranges:
1. "dates" = A1:A6
2. "values" = B1:B6

Your goal is to shift these ranges automatically by one row, once a new month begins (Apr'19). In order to achieve that, you need to modify your named ranges to use INDEX formulas:
1. "dates" -> =INDEX($A:$A,COUNT($A:$A)-5):INDEX($A:$A,COUNT($A:$A))
2. "values" -> =INDEX($B:$B,COUNT($B:$B)-5):INDEX($B:$B,COUNT($B:$B))

Hope it helps. Let me know if anything is unclear.