Dynamic charts - data in rows not columns
 

I am familiar with doing dynamic charts when the data is in columns, but have
another workbook that has the data in rows instead. How do I do a dynamic
chart from this data? I have quite a bit of information already in this
workbook so I do not want to redo the file to make it work.

Thanks for any help.

Dynamic charts - data in rows not columns
 

If you use OFFSET to define the dynamic ranges, you just need to adjust
which arguments are which. The syntax of OFFSET is

=OFFSET(reference range, row offset, column offset, row height, column
width)

Instead of defining a range in terms of a variable number of rows:

=OFFSET(Sheet1!$A$1,0,0,COUNT($A:$A),1)

you define it in terms of a variable number of columns:

=OFFSET(Sheet1!$A$1,0,0,1,COUNT($1:$1)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"Corrine" wrote in message
...
I am familiar with doing dynamic charts when the data is in columns, but
have
another workbook that has the data in rows instead. How do I do a dynamic
chart from this data? I have quite a bit of information already in this
workbook so I do not want to redo the file to make it work.

Thanks for any help.

Dynamic charts - data in rows not columns
 

Thank you for the solution. I'm sorry it took me so long to test it. It
works great. Of course, I want more :-)

I now want it to only count the last thirteen columns of data in the table.

thanks for your help, Corrine

"Jon Peltier" wrote:

If you use OFFSET to define the dynamic ranges, you just need to adjust
which arguments are which. The syntax of OFFSET is

=OFFSET(reference range, row offset, column offset, row height, column
width)

Instead of defining a range in terms of a variable number of rows:

=OFFSET(Sheet1!$A$1,0,0,COUNT($A:$A),1)

you define it in terms of a variable number of columns:

=OFFSET(Sheet1!$A$1,0,0,1,COUNT($1:$1)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"Corrine" wrote in message
...
I am familiar with doing dynamic charts when the data is in columns, but
have
another workbook that has the data in rows instead. How do I do a dynamic
chart from this data? I have quite a bit of information already in this
workbook so I do not want to redo the file to make it work.

Thanks for any help.

Dynamic charts - data in rows not columns
 

On Wed, 22 Aug 2007, in microsoft.public.excel.charting,
Corrine said:
Thank you for the solution. I'm sorry it took me so long to test it. It
works great. Of course, I want more :-)

I now want it to only count the last thirteen columns of data in the table.

Jon described an expression like

=OFFSET(Sheet1!$A$1, 0, 0, 1, COUNT($1:$1) )

which read all columns in the row. I think the last thirteen columns
will be an expression something like

=OFFSET(Sheet1!$A$1, 0, COUNT($1:$1)-13, 1, 13 )

i.e. it now starts thirteen columns from the right, and extends for
thirteen columns.

I may have made a fencepost error there, but I'm sure you'll be able to
fix it when you test the expression.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

Dynamic charts - data in rows not columns
 

"Fencepost error" - I've never heard this phrase, but I instantly understood
it. I'm always off by ± 1 in my OFFSETs....

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Del Cotter" wrote in message
...
On Wed, 22 Aug 2007, in microsoft.public.excel.charting,
Corrine said:
Thank you for the solution. I'm sorry it took me so long to test it. It
works great. Of course, I want more :-)

I now want it to only count the last thirteen columns of data in the
table.

Jon described an expression like

=OFFSET(Sheet1!$A$1, 0, 0, 1, COUNT($1:$1) )

which read all columns in the row. I think the last thirteen columns
will be an expression something like

=OFFSET(Sheet1!$A$1, 0, COUNT($1:$1)-13, 1, 13 )

i.e. it now starts thirteen columns from the right, and extends for
thirteen columns.

I may have made a fencepost error there, but I'm sure you'll be able to
fix it when you test the expression.

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.

Dynamic charts - data in rows not columns
 

Thank you, thank you - it works perfectly. No adjustments needed

"Del Cotter" wrote:

On Wed, 22 Aug 2007, in microsoft.public.excel.charting,
Corrine said:
Thank you for the solution. I'm sorry it took me so long to test it. It
works great. Of course, I want more :-)

I now want it to only count the last thirteen columns of data in the table.

Jon described an expression like

=OFFSET(Sheet1!$A$1, 0, 0, 1, COUNT($1:$1) )

which read all columns in the row. I think the last thirteen columns
will be an expression something like

=OFFSET(Sheet1!$A$1, 0, COUNT($1:$1)-13, 1, 13 )

i.e. it now starts thirteen columns from the right, and extends for
thirteen columns.

I may have made a fencepost error there, but I'm sure you'll be able to
fix it when you test the expression.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.