For an Excel Formula's variable taking a Range, input row number by the result of a formula

Question

I'm trying to fill in this =SERIES function to populate a line plot in Excel 2007.

=SERIES($BB$1,$BB$2:$BB$16,$BC$2:$BC$16,1)

Now the range $BB$2:$BB$16 and the one that follows are set for illustration. However, my plot needs to find a new range each time dynamically since the number of data points my function returns are different each time.

I can get the number of data points with a =COUNTA(BB2:BB1000), then I can chuck this number (plus 1 to make reference right) into the spot where 16 is in this case. How would I reference the following string as a range?

="$BB$2:$BB$" & COUNTA(BB2:BB1000)

In the place of

=SERIES([Cell],[Range],[Range]...)

If I just input this Excel won't take it. I tried INDIRECT but it returns the value of a cell, and can't be used as a range

Answer 1

This can be done using INDEX to specify the range

$BB$2:INDEX($BB:$BB,COUNTA($BB:$BB)+1)

While it can also be done with OFFSET or INDIRECT they are both volitile, so using INDEX is better because its not volitile.

For completness:
Using OFFSET

OFFSET($BB$2,0,0,COUNTA($BB:$BB))

Using INDIRECT

$BB$2:INDIRECT("BB"&COUNTA($BB:$BB)+1)

While these all work for "normal" cell formulas, it seems they don't work directly in a chart SERIES formula. However, they can still be used by creating a Worksheet scoped named range, and using that in the SERIES formula.

Answer 2

Try using the INDIRECT function:)

say you have three cell that can dynamically calculate the address of your input range. something like what you suggested and it is in the cell A1

="$BB$2:$BB$" & COUNTA(BB2:BB1000) ---> in cell A1

You can use the Indirect function like this:

=SERIES(INDIRECT(A1),[RANGE],[RANGE])

Hope this helps!