Dynamically set a range?

[BKGT]

Is there a way to dynamically set a range? For instance... can I have a formula that includes a range (Ax:Ay) where I can set the values of x and y based on the value of another cell so that in one case, the range may be A1:A80 and in another it may be A50:A60 and so on?

Thanks.

[Richard Buttrey]

On Thu, 27 Apr 2006 08:17:57 -0500, BKGT
<[email protected]> wrote:

>
>Is there a way to dynamically set a range? For instance... can I have a
>formula that includes a range (Ax:Ay) where I can set the values of x
>and y based on the value of another cell so that in one case, the range
>may be A1:A80 and in another it may be A50:A60 and so on?
>
>Thanks.

Use the Indirect Function

If your x & y variable cells are say B1 & B2

then with B1=50 and B2=60 and if (say) you're summing A50:A60, your
formula would be

=SUM(INDIRECT("A"&B1&":A"&B2))

HTH


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

[Arvi Laanemets]

Hi

Like this:

=SUM(INDIRECT(A1))
, where A1 contains string like "A1:A80"


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"BKGT" <[email protected]> wrote in message
news:[email protected]...
>
> Is there a way to dynamically set a range? For instance... can I have a
> formula that includes a range (Ax:Ay) where I can set the values of x
> and y based on the value of another cell so that in one case, the range
> may be A1:A80 and in another it may be A50:A60 and so on?
>
> Thanks.
>
>
> --
> BKGT
> ------------------------------------------------------------------------
> BKGT's Profile:
> http://www.excelforum.com/member.php...o&userid=33862
> View this thread: http://www.excelforum.com/showthread...hreadid=536778
>

[BKGT]

Great. That worked for me.

Thanks for the help and the quick responses.