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 Insert>Name>Define>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 "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 Insert>Names>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 Insert>Name>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.
http://www.stfx.ca/people/bliengme/ExcelTips/Dynamic.htm
http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html
...Kelly
(e-mail address removed)
