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.

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" <Corrine@discussions.microsoft.com> wrote in message
news:E91B3AD2-AFE0-4207-A5C1-DA5E4C4C9573@microsoft.com...
>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.

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" <Corrine@discussions.microsoft.com> wrote in message
> news:E91B3AD2-AFE0-4207-A5C1-DA5E4C4C9573@microsoft.com...
> >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.
>
>
>

On Wed, 22 Aug 2007, in microsoft.public.excel.charting,
Corrine <Corrine@discussions.microsoft.com> 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 del@branta.demon.co.uk,
which goes to a spam folder-- please send your email to del3 instead.

"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" <del@branta.demon.co.uk> wrote in message
news:8rIyflFWqMzGFw$A@branta.demon.co.uk...
> On Wed, 22 Aug 2007, in microsoft.public.excel.charting,
> Corrine <Corrine@discussions.microsoft.com> 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
> del@branta.demon.co.uk,
> which goes to a spam folder-- please send your email to del3 instead.

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

"Del Cotter" wrote:

> On Wed, 22 Aug 2007, in microsoft.public.excel.charting,
> Corrine <Corrine@discussions.microsoft.com> 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 del@branta.demon.co.uk,
> which goes to a spam folder-- please send your email to del3 instead.
>