Graphing function for a variable number of rows

Question

Looking for a way to make the number of rows being graphed variable based on the number of rows in the worksheet. My data presently has 221 rows and my series is:

=SERIES('Sheet1'!$A$1,,'Sheet1'!$A$2:$A$221,1)

But what I really want is to have the cell A221 be set based on the number of rows of data like this:

=SERIES('Sheet1'!$A$1,,'Sheet1'!$A$2:$A$NUMROWS,1)

Is there a way to do this?

I am able to display the cell reference I need using the ADDRESS function. The formula

=ADDRESS(COUNTA(A1:A1000),1)

will give me this cell reference $A$221. Trying to use this as part of the SERIES like this

=SERIES('Sheet1'!$A$1,,'Sheet1'!$A$2:(ADDRESS(COUNTA(A1:A1000)),1),1)

produces an error in the formula.

Answer 1

Hi Robert,

How about creating a table to make a chart with dynamic ranges? The following are some steps of my test:

1.Select your data and click Insert>Table.

2.After that, the table should be selected. And you can click Insert>choose a chart type.

3.Add a row in the table, you will find that the chart is changed automatically.

Hope it helps.

Regards,

Zixuan

Answer 2

Zixuan,

Yes, that works! My data was not explicitly in a table so I first I created a table of the data, and now when I add or delete rows, the range being graphed follows the number of rows. Perfect!

Thank you very much for your help! Such a simple solution for what was a complicated problem.

Robert

Answer 3

Hi,

I cannot spot the error.  Sorry.

Answer 4

Thank you for the reply, but Excel indicates "that function isn't valid."

Answer 5

Hi,

Does this work?

=SERIES('Sheet1'!$A$1,,INDIRECT("'Sheet1'!"&ADDRESS(2,1)&":"&ADDRESS(COUNTA(A1:A1000),1)),1)