rolling graphs

[amits]

I have rolling stacked column & line graph to which I add data each month. Then I have to update the source data manually to get the new data included in graphs. I want to automate this step, so I dont have to update the "source data" each time when data is added.

My data looks like:
Jan-08 Feb-08 Mar-08 Apr-08 May-08 Jun-08 Jul-08
Strong 78% 80% 55% 65% 78% 75% 66%
New 25% 25% 22% 21% 20% 23% 24%
Strong & old 10% 12% 11% 13% 14% 15% 10%
New & old 20% 13% 14% 12% 14% 12% 11%


Strong & new are in columns whereas "strong & old" and "new & old" are in line format.

[Andy Pope]

Search the forum for Dynamic charts.

You need to set up a bunch of named ranges which define the charts label and data series ranges.

[amits]

Thanks Andy.

But do you have any example especially with "months" in a row instead of in "columns". I am struggling with OFFSET formula as shown in one of the example by "peltiertech"

[Andy Pope]

In the OFFSET formula you need to switch the Row/Column bits.

=OFFSET(<anchorcell> , 0, 0, 1 , COUNTA(<columnRef>) )

if you are still stuck post example workbook of what you have managed so far.

[amits]

Hi Andy,

I am completely lost. Attached is the graph I want to have.

[Andy Pope]

Here are the named ranges

CHT_LABEL =OFFSET(Sheet1!$B$1,0,0,1,COUNTA(Sheet1!$1:$1))
CHT_DATA1 =OFFSET(CHT_LABEL,1,0)
CHT_DATA2 =OFFSET(CHT_LABEL,2,0)
CHT_DATA3 =OFFSET(CHT_LABEL,3,0)
CHT_DATA4 =OFFSET(CHT_LABEL,4,0)

[amits]

Andy,

Thanks heaps.
Just a final questions...atleast for the day. If I need to do the same thing for 20 graphs do I need to follow these steps for each graph individually or is there any smart way of doing this.(I have atleast 5 graphs on the same sheet).

[Andy Pope]

each series requires a unique name.

If the data has the same columns but different rows then you can use the shortened versions based of the CHT_LABEL range.

Only way to speed things would be via code.

[amits]

Can you advice me of what code I can use here.

[amits]

Hi Andy,

Actually I needed to have the rolling graph, which mean that if I add new months data, the oldest months data get excluded.

But finally I have figures it out myself using the formaula below:

cht_label =OFFSET(Sheet1!$C$29,0,(COUNTA(Sheet1!$29:$29)-chtLen),1,chtLen)

where chtLen = cell b2, where value is 12

[Andy Pope]

Did you still need help with coding?

[amits]

I have manually updated the graphs now. But if you can still send the codes that would be great...may be I can use them in future.

[Andy Pope]

add named range

Please Login or Register  to view this content.

Add series referencing named range.

Please Login or Register  to view this content.