Dynamic charts - data in rows not columns

[Guest]

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.

 

[Jon Peltier]

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/
_______

 

[Guest]

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

 

[Del Cotter]

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.

 

[Jon Peltier]

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

- Jon

 

[Guest]

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