how to create dynamic chart range when 1 axis is constant

**jrtaylor** · 10-10-2011, 01:35 AM

I've attached a worksheet to illustrate my question.

I've got a large worksheet which has lots of columns. Some of the columns are perhaps 3,000 rows deep. One of these columns serves as the x axis for a line graph.

Two other columns are the y axis data points. These columns can vary from 50 rows to 3000 rows, depending on the "granularity" of the data question.

I have difficulty remembering how to create named ranges for dynamic charts. And I don't know if a named range is part of the solution.... but...

Is there a way to accomplish a "partially" dynamic chart, and if so what would the formula look like? Feel free to modify my example sheet.

Thanks so much for your help!

**Andy Pope** · 10-11-2011, 03:35 AM

Yes you could use named ranges and vary the number of rows used in the axis and data points.

perhaps we could make the last row dependent on the last number in column H

Your problem is identifying the end of the data in a column. Column H is full of numbers.
You could delete the zeros or replace with =NA()

**jrtaylor** · 10-14-2011, 06:26 PM

Hi, I modified column H to include an "isnumber" function dependent on other cells, so now I have columns which only populate with dates if there is data for the x axis.

SO now all I have to do is try to wrap my head around dynamic chart ranges and I think the problem will be solved.

Thanks!