Hello,

I have a spreadsheet that has its data in the following format:

2003 2004 2005 2006
Company 1 1.5 1.63 2.1
Company 2 .75 .89 1.09

How can I create a dynamic chart so that when I input the data for 2006 (and
forward) the chart will automatically update. Thank you in advance for any
assistance provided.

Dave Y

Dave:

I'll walk your through setting up a dynamic chart, however, I'd liek to
suggest you rearrange your data table as follows:

Year Company 1 Company2
2003 1.5 0.75
2004 1.63 0.89
2005 2.1 1.09
2006

This way, year is the X variable and Company1 is Y1 and Company2 is Y2.
You are working with a pretty small dta set, however, if you were working
with months instead of years, you would want the data variable going down
the sheet, not across.

Now to dynamic charting.

The trick is to set up range names for the year, company1 and company2. You
do this with InsertNameDefineNew .
You enter the name for your variable in hte name box and enter an offset
formula in therefers to box. I'd suggest your do not use "year" because
Excel uses that word for a function. Yr, X_Yer, or something like that is
fine.

The Offset function is very powerful. You may want to read up on it. For
your situation
= Offset(sheet1!$A$2,0,0,Counta(sheet1!$A$2:$A$100))

This formula tells Excel that my range name starts A2 of Sheet1, has 0 row &
0 column offset and extends for counta of nonblank cells in range(A2:A100).

There are several variatioons of this basic offset formula that will work.

Next you need to make dynamic rang names for company1 and company2. For
company1, go through same InsertNamesDefine steps above. For referes to

= Offset(x_year,0,1) - This tells Excel that company1
rnage name refers to x_year range with 0 rows offset and 1 column offset.

Company2 is the same except a 2 for offset.

You now have dynaamic rang names. Be sure to check them out before you
proceed to make sure that they workd. You can do this be InsertNameDefine
and on thr right side of Refers To box, select naviagation icon. Excel
should brin g you to the corect range. If not, your offset formula needs
some editing.

Now you make your chart(s) the regular way.

Once you have your charts made, you can go to the series formulas replace
the cell references for X and Y with your range names.

There are plenty of how to tuorials on line.

http://www.stfx.ca/people/bliengme/E...ps/Dynamic.htm
http://peltiertech.com/Excel/Charts/...umnChart1.html


...Kelly





"Dave Y" wrote in message
...
Hello,

I have a spreadsheet that has its data in the following format:

2003 2004 2005 2006
Company 1 1.5 1.63 2.1
Company 2 .75 .89 1.09

How can I create a dynamic chart so that when I input the data for 2006
(and
forward) the chart will automatically update. Thank you in advance for any
assistance provided.

Dave Y

Hi Kelly,

Thank you for your in depth response on dynamic charting (and the links
too.). I will re-arrange my data as you have suggested and follow your
explanation. Also, thank you for explaining the OFFSET formula as well. I am
in no way an Excel guru and I really appreciate it when someone takes the
time to explain their response. Thanks again.

Dave Y

" wrote:

Dave:

I'll walk your through setting up a dynamic chart, however, I'd liek to
suggest you rearrange your data table as follows:

Year Company 1 Company2
2003 1.5 0.75
2004 1.63 0.89
2005 2.1 1.09
2006

This way, year is the X variable and Company1 is Y1 and Company2 is Y2.
You are working with a pretty small dta set, however, if you were working
with months instead of years, you would want the data variable going down
the sheet, not across.

Now to dynamic charting.

The trick is to set up range names for the year, company1 and company2. You
do this with InsertNameDefineNew .
You enter the name for your variable in hte name box and enter an offset
formula in therefers to box. I'd suggest your do not use "year" because
Excel uses that word for a function. Yr, X_Yer, or something like that is
fine.

The Offset function is very powerful. You may want to read up on it. For
your situation
= Offset(sheet1!$A$2,0,0,Counta(sheet1!$A$2:$A$100))

This formula tells Excel that my range name starts A2 of Sheet1, has 0 row &
0 column offset and extends for counta of nonblank cells in range(A2:A100).

There are several variatioons of this basic offset formula that will work.

Next you need to make dynamic rang names for company1 and company2. For
company1, go through same InsertNamesDefine steps above. For referes to

= Offset(x_year,0,1) - This tells Excel that company1
rnage name refers to x_year range with 0 rows offset and 1 column offset.

Company2 is the same except a 2 for offset.

You now have dynaamic rang names. Be sure to check them out before you
proceed to make sure that they workd. You can do this be InsertNameDefine
and on thr right side of Refers To box, select naviagation icon. Excel
should brin g you to the corect range. If not, your offset formula needs
some editing.

Now you make your chart(s) the regular way.

Once you have your charts made, you can go to the series formulas replace
the cell references for X and Y with your range names.

There are plenty of how to tuorials on line.

http://www.stfx.ca/people/bliengme/E...ps/Dynamic.htm
http://peltiertech.com/Excel/Charts/...umnChart1.html


...Kelly





"Dave Y" wrote in message
...
Hello,

I have a spreadsheet that has its data in the following format:

2003 2004 2005 2006
Company 1 1.5 1.63 2.1
Company 2 .75 .89 1.09

How can I create a dynamic chart so that when I input the data for 2006
(and
forward) the chart will automatically update. Thank you in advance for any
assistance provided.

Dave Y