software

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 Insertgt;Namegt;Definegt;New .
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 quot;yearquot; 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 amp;
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 Insertgt;Namesgt;Define 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 Insertgt;Namegt;Define
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.

www.stfx.ca/people/bliengme/E...ps/Dynamic.htm
peltiertech.com/Excel/Charts/...umnChart1.html...Kellyquot;Dave Yquot; gt; wrote in message
news
gt; Hello,
gt;
gt; I have a spreadsheet that has its data in the following format:
gt;
gt; 2003 2004 2005 2006
gt; Company 1 1.5 1.63 2.1
gt; Company 2 .75 .89 1.09
gt;
gt; How can I create a dynamic chart so that when I input the data for 2006
gt; (and
gt; forward) the chart will automatically update. Thank you in advance for any
gt; assistance provided.
gt;
gt; 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

quot; wrote:

gt; Dave:
gt;
gt; I'll walk your through setting up a dynamic chart, however, I'd liek to
gt; suggest you rearrange your data table as follows:
gt;
gt; Year Company 1 Company2
gt; 2003 1.5 0.75
gt; 2004 1.63 0.89
gt; 2005 2.1 1.09
gt; 2006
gt;
gt; This way, year is the X variable and Company1 is Y1 and Company2 is Y2.
gt; You are working with a pretty small dta set, however, if you were working
gt; with months instead of years, you would want the data variable going down
gt; the sheet, not across.
gt;
gt; Now to dynamic charting.
gt;
gt; The trick is to set up range names for the year, company1 and company2. You
gt; do this with Insertgt;Namegt;Definegt;New .
gt; You enter the name for your variable in hte name box and enter an offset
gt; formula in therefers to box. I'd suggest your do not use quot;yearquot; because
gt; Excel uses that word for a function. Yr, X_Yer, or something like that is
gt; fine.
gt;
gt; The Offset function is very powerful. You may want to read up on it. For
gt; your situation
gt; = Offset(sheet1!$A$2,0,0,Counta(sheet1!$A$2:$A$100))
gt;
gt; This formula tells Excel that my range name starts A2 of Sheet1, has 0 row amp;
gt; 0 column offset and extends for counta of nonblank cells in range(A2:A100).
gt;
gt; There are several variatioons of this basic offset formula that will work.
gt;
gt; Next you need to make dynamic rang names for company1 and company2. For
gt; company1, go through same Insertgt;Namesgt;Define steps above. For referes to
gt;
gt; = Offset(x_year,0,1) - This tells Excel that company1
gt; rnage name refers to x_year range with 0 rows offset and 1 column offset.
gt;
gt; Company2 is the same except a 2 for offset.
gt;
gt; You now have dynaamic rang names. Be sure to check them out before you
gt; proceed to make sure that they workd. You can do this be Insertgt;Namegt;Define
gt; and on thr right side of Refers To box, select naviagation icon. Excel
gt; should brin g you to the corect range. If not, your offset formula needs
gt; some editing.
gt;
gt; Now you make your chart(s) the regular way.
gt;
gt; Once you have your charts made, you can go to the series formulas replace
gt; the cell references for X and Y with your range names.
gt;
gt; There are plenty of how to tuorials on line.
gt;
gt; www.stfx.ca/people/bliengme/E...ps/Dynamic.htm
gt; peltiertech.com/Excel/Charts/...umnChart1.html
gt;
gt;
gt; ...Kelly
gt;
gt;
gt;
gt;
gt;
gt; quot;Dave Yquot; gt; wrote in message
gt; news
gt; gt; Hello,
gt; gt;
gt; gt; I have a spreadsheet that has its data in the following format:
gt; gt;
gt; gt; 2003 2004 2005 2006
gt; gt; Company 1 1.5 1.63 2.1
gt; gt; Company 2 .75 .89 1.09
gt; gt;
gt; gt; How can I create a dynamic chart so that when I input the data for 2006
gt; gt; (and
gt; gt; forward) the chart will automatically update. Thank you in advance for any
gt; gt; assistance provided.
gt; gt;
gt; gt; Dave Y
gt;
gt;
gt;