Source Range

[Alan Auerbach]

I have a sheet with four columns of data. The first column is date/time
information for the X-Axis. Every day, two new rows are added at the bottom.
I have to manually change the source data range each time to include these
newly added rows of data. Is there a way to specify that all rows containing
data are to be included? I could specify, say, one-hundred additional rows
but that would create a large empty area at the right side of the chart and
unnecessarily compress the data to the left. Is there an alternate solution?

Thanks in advance for any comments,
/s/ Alan Auerbach

 

[Jon Peltier]

http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html
http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon

 

[Alan Auerbach]

Dear Jon,

Thank you so very much for the link that perfectly addresses my question.

Jerry Latham referred me to you. He told me that if I post this question on
the forum, I should cross my fingers that you see it! He was so right. You
and he have been so very helpful and generous. I cannot thank you enough!

Best wishes,
/s/ Alan Auerbach

 

[Jon Peltier]

Alan -

Thanks for following up. Makes me especially glad I offered my assistance.

- Jon

 

[Alan Auerbach]

Dear Jon,

Perhaps I can add something to the discussion.

I am certain this will be obvious to you, but it was not to me so it might
help someone else who reads this thread.

In my chart, not every cell contains data. Some values are updated
once-a-day while others are updated twice-a-day and still others weekly.
Since the date and time values are present in every row, they can be used to
select the number of rows. In this case, I merely used that column (A, in my
case) to derive the COUNTA value rather than a colum with data points. In
this way, the graph included values in every row and can trend between values
(or simply data-point) for those columns in which not all cells contain
values. All that is necessary is to use the formula in the example you
provided but maintain the COUNTA references the same for each data source,
regardless of the column in which the data appears.

I hope that this comment is of some value.

/s/ Alan Auerbach

 

[Jon Peltier]

What I usually do is base all of my dynamic ranges on the X values or
categories, as you do with the dates. I define the X value range by
explicitly counting values in the X value column. Then subsequent ranges (Y
values) are simply based on an offset of this range:

Given a properly defined range named Xvalues:

Name: Yvalues1
Refers To:
=OFFSET(Xvalues,0,1)

Name: Yvalues2
Refers To:
=OFFSET(Xvalues,0,2)

etc.

I initially did it this way to save typing, and only afterward realized that
it made for more robust range definition.

- Jon