dynamic charts

  • Thread starter Thread starter Kirk
  • Start date Start date
K

Kirk

I'm trying to construct a dynamic chart with a scroll function. Right
now row 1 contains my labels and row 2 contains my data. For example,
a b c d
1 jan feb mar apr
2 $20 $30 $40 $50

I'm able to do it if I convert my data to where the months are in
column a and the data is in column b.
a b
1 jan $20
2 feb $30
3 mar $40
4 apr $50
All of the dynamic chart information that i have been able to find
shows the data in columns. Is there a way where I don't have to
convert my data to have it show the month in column a and the data in
column b
 
Dynamic range names work the same way for column based data or row based
data, you just have to keep track of what is changing.

The generic offset formula is =Offset(sheet!$start cell, row_offset,
col_offset, number rows, number_cols)

For a dynamic column data set we use the =
offset(sheet1!$a$1,0,0,counta(sheet1!$a:$a),1)

For a dynamic row we use a similar formula, however, we vary the cols, not
the rows
= offset(sheet1!$a$1,0,1,counta(sheet1!$1:$1))


Once you set your months, you can set your data row as an offset to the
months:

= offset(months,1,0)

....Kelly
 
The dynamic formulas use OFFSET, right? Look up OFFSET in the help files,
and see which arguments you need to adjust to make the formulas work in the
transposed orientation. Hint:

=OFFSET($A$1,RowOffset,ColOffset,RowsHigh,ColsWide)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______
 
Back
Top