automatically adjust charts

[Ravens Fan]

I am always charting 2 years of data. I have 2 spread sheets, one has multi
charts and the other is all the data for the charts. I'm tracking about 6
different data points in a line chart. Each month the data sheet gets the
previous months production data. I'd like to have it automatically adjust the
charts. Drop the oldest month and add the newest month to the chart. That
would save me the time of manually updating the charts.

 

[Kelly O'Day]

Ravens Fan:

You can make things a lot easier for yourself by creating a dynamic chart
that plots the 24 months of data, or whatever time period you want.

This post shows you how to set up a dynamic range that plots last 12 days.
You should be able to adjust it to handle monthly data and use a 24 month
period.

http://processtrends.com/pg_dynamic_charts.htm

Post back if you need any help walking through the dynamic range setup.

Kelly

http://processtrends.com

 

[Ravens Fan]

Thanks for getting back. I am having issues with creating a dynamic chart.
This is what I have and what I tried. I’m new at creating excel charts, so
any help is greatly appreciated.

My data range is A6:K54. I have to specify that range because there are
other sets of data on the spreadsheet for other charts. Column "A" are the
months, I have the formula set to leave a blank cell if no data was collected
that month. A6=1st month and A54=last month. I’m setting it up for collecting
a total of 4 years of data. I’m still collecting the blank cell if there was
no data collected that month. I'm collecting 2 years of data, A6 being the
first month and counting 24 months down. Once it hits the 25th month I need
it to start dropping the first month to keep 24 months only. So at that point
it should read A7:A30.

File name is: test.xls
Sheet name is: MD Extract

Defining X Value I created a name “months†with the following formula:

=OFFSET('MD Extract'!$A$6,0,0,COUNTA(MD Extract!$A$6:$A$54)-1)

Defining the Y Value I created a name “range†with the following formula:

=OFFSET(test.xls!months,0,b)


This is where I’m having my issue and I’m probably not understanding it
correctly. I inserted column “B†on my spreadsheet and entered the offset
formula:

=SERIES(MD extract!$a$6,test.xls!months,test.xls!range,1)

And the error I get is “That function is not available†highlighting SERIES.


Thanks again and I appreciate any help you can give me.

 

[Kelly O'Day]

Ravens Fans

I'm a Pats fan. Sorry I can't wish you good luck tonight!

As for your dynamic chart:

When making range names, I always check them to make sure they work. You can
do this by going to Insert>Name>Define then select your name and hit the
navigation box on right side of Formula box. It will highlight the named
range if everything is ok. If not, it will show an empty dialogue box with
just the range name and address.

I suggest that you check the "months" and "range" names to make sure that
they works. Correct if necessary.

I'm not clear where you entered the series formula "=SERIES(MD
extract!$a$6,test.xls!months,test.xls!range,1)".

Once you have both name ranges working properly, make your chart the normal
way. Once the chart is working properly, you can edit the chart series
formula to refer to the named ranges.

Kelly

http://procestrends.com
..

 

[Ravens Fan]

I must not be a true Ravens Fan. I picked NE on my football pools. But, when
money is involved, money talks.

I wish I could put screen prints on here. It might be easier to show you
what I'm doing.

OK, I checked the formula box, it doesn't hightlight the range of data I'm
using. So apparently I'm doing something wrong in my formula for "months".

I'm just not familiar with excel charts, so, I think I'm misunderstanding
the code.

I'm going try and put a small example of what data I'm using. Some of the
columns I’m using to create my line chart. I need to count 24 months of data
and chart it. I’m only up to September, so I don’t quite have 24 months yet.
A6 = Dec 05 to K22 = 5,911.

X Value:
=OFFSET(MD Extract!$A$6,0,0,COUNTA(MD Extract!$A$6:$K$54)-1)

Y Value:
=OFFSET(test.xls!months,0,b)

Column
A B C D E F G
H I J K
Dec 05 46,795 677 677 677 675 11 709 641 4,254
Jan 06 114,095 658 658 667 675 24 709 641 4,754
Feb 61,626 677 677 671 675 11 709 641 5,602
Mar 163,078 701 701 679 675 34 709 641 4,796
Apr 117,068 761 761 713 675 17 709 641 761 6,886
May 121,455 720 720 727 675 24 709 641 720 5,061

Jul 95,900 699 699 700 675 16 709 641 5,994
Aug 168,740 719 719 700 675 31 709 641 719 5,443
Sep 155,735 726 726 715 675 28 709 641 726 5,562
Oct 186,894 751 751 732 675 30 709 641 751 6,230
Nov 168,060 762 762 746 675 29 709 641 762 5,795
Dec 06 109,893 681 681 731 675 17 709 641 6,464
Jan 07 98,727 703 703 715 710 15 746 675 6,582
Feb 130,748 696 696 693 710 20 746 675 6,537
Mar 127,146 724 724 708 710 25 746 675 5,086
Apr 101,773 732 732 717 710 20 746 675 5,089
May 122,703 724 724 727 710 23 746 675 5,335
Jun 87,684 731 731 729 710 15 746 675 5,846
Jul 77,124 735 735 730 710 16 746 675 4,820
Aug 123,084 764 764 743 710 22 746 675 764 5,595
Sep 118,210 765 765 755 710 20 746 675 765 5,911


Anything you can help me with I would greatly aprreciate.

--
Baltimore Ravens

Ravens Fans

I'm a Pats fan. Sorry I can't wish you good luck tonight!

As for your dynamic chart:

When making range names, I always check them to make sure they work. You can
do this by going to Insert>Name>Define then select your name and hit the
navigation box on right side of Formula box. It will highlight the named
range if everything is ok. If not, it will show an empty dialogue box with
just the range name and address.

I suggest that you check the "months" and "range" names to make sure that
they works. Correct if necessary.

I'm not clear where you entered the series formula "=SERIES(MD
extract!$a$6,test.xls!months,test.xls!range,1)".

Once you have both name ranges working properly, make your chart the normal
way. Once the chart is working properly, you can edit the chart series
formula to refer to the named ranges.

Kelly

http://procestrends.com
..

Thanks for getting back. I am having issues with creating a dynamic chart.
This is what I have and what I tried. I'm new at creating excel charts, so
any help is greatly appreciated.

My data range is A6:K54. I have to specify that range because there are
other sets of data on the spreadsheet for other charts. Column "A" are the
months, I have the formula set to leave a blank cell if no data was
collected
that month. A6=1st month and A54=last month. I'm setting it up for
collecting
a total of 4 years of data. I'm still collecting the blank cell if there
was
no data collected that month. I'm collecting 2 years of data, A6 being the
first month and counting 24 months down. Once it hits the 25th month I
need
it to start dropping the first month to keep 24 months only. So at that
point
it should read A7:A30.

File name is: test.xls
Sheet name is: MD Extract

Defining X Value I created a name "months" with the following formula:

=OFFSET('MD Extract'!$A$6,0,0,COUNTA(MD Extract!$A$6:$A$54)-1)

Defining the Y Value I created a name "range" with the following formula:

=OFFSET(test.xls!months,0,b)


This is where I'm having my issue and I'm probably not understanding it
correctly. I inserted column "B" on my spreadsheet and entered the offset
formula:

=SERIES(MD extract!$a$6,test.xls!months,test.xls!range,1)

And the error I get is "That function is not available" highlighting
SERIES.


Thanks again and I appreciate any help you can give me.

 

[Kelly O'Day]

Just e-mail me the file and I'll see if I can fix it.

koday*at*processtrends*dot*com (edit email)

Kelly

http://procesxtrends.com

 

[Ravens Fan]

I sent it to you. Thanks
--
Baltimore Ravens

Just e-mail me the file and I'll see if I can fix it.

koday*at*processtrends*dot*com (edit email)

Kelly

http://procesxtrends.com