6 month data on a graph

Goose0310

New Member
Joined
Mar 15, 2019
Messages
1
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!
 

Some videos you may like

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
Joined
Aug 28, 2016
Messages
654
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,991
Messages
5,526,105
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top