Charting Range

[yanclarechen]

I have an Excel spreadsheet set up to collect shipping data on weekly basis. I also set up a bar chart to show the weekly shipment graphically. But I only want 14 weeks'data to show up on my bar chart, ie. starting from the current week, count back 14 weeks and update the bar chart. Or in another word, every week when I add the previous week's shipment data to the spreadsheet, I want the chart to be automatically updated, with the lastest week's data bar being added and the oldest bar being deleted. Is there any function or VBA code can be use to achieve that?
my email address: yanclarechen@lucent.com

 

[Mark W.]

Book1
	A	B	C	D	E	F	G
1	WeekNum	UnitsShipped	Last14		Last14	TRUE
2	8	54	FALSE
3	9	61	FALSE		SumofUnitsShipped
4	10	91	FALSE		WeekNum	Total
5	11	54	FALSE		22	69
6	12	57	FALSE		23	75
7	13	95	FALSE		24	64
8	14	72	FALSE		25	79
9	15	79	FALSE		26	97
10	16	89	FALSE		27	100
11	17	86	FALSE		28	57
12	18	97	FALSE		29	52
13	19	93	FALSE		30	73
14	20	64	FALSE		31	77
15	21	84	FALSE		32	71
16	22	69	TRUE		33	78
17	23	75	TRUE		34	50
18	24	64	TRUE		35	51
19	25	79	TRUE
20	26	97	TRUE
21	27	100	TRUE
22	28	57	TRUE
23	29	52	TRUE
24	30	73	TRUE
25	31	77	TRUE
26	32	71	TRUE
27	33	78	TRUE
28	34	50	TRUE
29	35	51	TRUE
30
Sheet1

 

[Damon Ostrander]

Hi yanclarechen,

It does not require VBA to do this. You can use a Dynamic Named Range to automatically adjust the range to include the last 14 cells as you add or subtract data. Then you can use this range in defining your chart series.

For example, suppose your x values (dates?) are in column A starting in A2 and y values (shipment data) are in column B starting in B2. First use Insert > Define > Name to define a name (say, MyYdata) and in the Refers To box enter

=OFFSET(Sheet1!$B$2,COUNT(Sheet1!$B:$B)-15,0,14,1)

Similarly for your x data define a name (say, MyXdata) and in the Refers To box enter

=OFFSET(MyYdata,0,-1)

which simply offsets from the MyXdata one column to the left.

Now select your data series on the Chart and in the formula bar replace the x and y series data references with the new Dynamic Named Ranges, like this:

=SERIES(,Sheet1!MyXdata,Sheet1!MyYData,1)

Now you will find that as you add or delete data at the ends of the two ranges, the chart automatically adjusts to show exactly 14 weeks of data.