6 month data on a graph

Goose0310

New Member
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!

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

JustynaMK

Well-known Member
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.

Replies
8
Views
127
Replies
3
Views
43
Replies
4
Views
39
Replies
4
Views
36
Replies
0
Views
42