Charts which automatically adapt to variable source data length

I create a line chart with X-Y data in Cells A1:B100, but since I am
importing electronic data sometime I only have 50 rows of data (Cells A1:B50)
which leaves a lot of unused chart space. Can I create a chart which
automatically adapts its source data reference to the number of rows of real
data (Cells A1:B50) in the specified source data interval (Cells A1:B100)?

Conceptually, I could construct a formula in the chart source data fields
such as: =A1:address(Count(A1:A100),1). This would count up the number of
rows of real data in column "A" and with the address function generate a cell
address for the last cell of real data "A50" creating the source data address
=A1:A50. Unfortunally I cant get the program to accept any functions in the
chart source data fields.

Any suggestions would be appreciated!!

Hi,

You need to create a dynamic named range upon which you can chart.
Check out Jon's example
http://peltiertech.com/Excel/Charts/Dynamics.html

Cheers
Andy

Excel Engineer wrote:

I create a line chart with X-Y data in Cells A1:B100, but since I am
importing electronic data sometime I only have 50 rows of data (Cells A1:B50)
which leaves a lot of unused chart space. Can I create a chart which
automatically adapts its source data reference to the number of rows of real
data (Cells A1:B50) in the specified source data interval (Cells A1:B100)?

Conceptually, I could construct a formula in the chart source data fields
such as: =A1:address(Count(A1:A100),1). This would count up the number of
rows of real data in column "A" and with the address function generate a cell
address for the last cell of real data "A50" creating the source data address
=A1:A50. Unfortunally I cant get the program to accept any functions in the
chart source data fields.

Any suggestions would be appreciated!!

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info