Variable Address Ranges

This question is about specifying formula address ranges dynamically.
I am looking to do a series of Linest() regressions between one index with five years of data (row 10) and a large number of factors with variable data in terms of both start and end date (rows 20-165).
Linest() will fail if I use it for the constant five year (60 columns) data set because the factors are sparse. I need a way to set the address range in Linest() as a formula or indirect address so that I can have other columns with the address of the first and last available data.
This data is updated monthly so specifiying range sby hand for >100 factors is not practicable.
Does anyone have any experience with this type of range addressing issue in Excel formulae or VBA?
Thank you.

Did you consider the INDIRECT() function?

Look in HELP for details

--
Kind regards,

Niek Otten

"pncd2" <pncd2.201fra_1134577200.651@excelforum-nospam.com> wrote in message
news:pncd2.201fra_1134577200.651@excelforum-nospam.com...
>
> This question is about specifying formula address ranges dynamically.
> I am looking to do a series of Linest() regressions between one index
> with five years of data (row 10) and a large number of factors with
> variable data in terms of both start and end date (rows 20-165).
> Linest() will fail if I use it for the constant five year (60 columns)
> data set because the factors are sparse. I need a way to set the
> address range in Linest() as a formula or indirect address so that I
> can have other columns with the address of the first and last available
> data.
> This data is updated monthly so specifiying range sby hand for >100
> factors is not practicable.
> Does anyone have any experience with this type of range addressing
> issue in Excel formulae or VBA?
> Thank you.
>
>
> --
> pncd2
> ------------------------------------------------------------------------
> pncd2's Profile:
> http://www.excelforum.com/member.php...o&userid=29618
> View this thread: http://www.excelforum.com/showthread...hreadid=493443
>