Rolling chart to show 13 months back from current month

  • Thread starter Thread starter PBcorn
  • Start date Start date
P

PBcorn

I am using names andd offset to create a 13 month chart which rolls forward
automatically. however it only updates when a new month is added, i want it
to display the 13 months up to a particular month - held in a separate cell.

currently:

x-axis labels uses the name chtCts
=OFFSET('Data'!$A$3,COUNTA('Data'!$A$3:$A$27)-1,0,-MIN(chtLenn,COUNTA('Data'!$A$3:$A$27)-1),1)

data:
=OFFSET(chtCts,0,1)


table looks like this:
COLA COLB
Month Data
J 3
F 4
M 5
A 6
M .
J .
J .
A
S
O
N
D
J

So at the moment it displays jan to jan, when F is added to above table,
will show feb to feb etc. What i want is to enter a month in a cell, eg M,
and for the chart to display 13 months dependent on this, in this example M
to M. Have tried countif but for some reason there seems to be alimit to the
length of the formula xl2002 will accept as a name. Advice appreciated.
 
You need to change the offset formula, so instead of starting at the last
point and counting backwards, you instead count from the month you select
and count backward. In your date column, put a real date, like 1-Jan-09 for
last month (use a custom number format of MMMMM to show just the first
initial of the month name). Then use a match on a date which is in, say, C1:

=OFFSET(Data!$A$3,MATCH(Data!$C$1,Data!$A$3:$A$27)-1,0,-MIN(13,COUNTA(Data!$A$3:$A$27)-1),1)

- Jon
 
Thanks Jon. This works fine.

Regards PB

Jon Peltier said:
You need to change the offset formula, so instead of starting at the last
point and counting backwards, you instead count from the month you select
and count backward. In your date column, put a real date, like 1-Jan-09 for
last month (use a custom number format of MMMMM to show just the first
initial of the month name). Then use a match on a date which is in, say, C1:

=OFFSET(Data!$A$3,MATCH(Data!$C$1,Data!$A$3:$A$27)-1,0,-MIN(13,COUNTA(Data!$A$3:$A$27)-1),1)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______
Click to expand...
 
Back
Top