Defining a range using a cell reference

Hi all,

I am trying to define a range but the range needs to be dynamic. Instead of simply defining the range as (A1:A15) I need to express my range as (A1: "A1+the value in another cell, A20).

Please could you let me know how to achieve this.
Thanks!
Jag

Hi
Try
=INDIRECT("A1:A"&A20)

--
Regards

Roger Govier


"jagbabbra" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all,
>
> I am trying to define a range but the range needs to be dynamic.
> Instead of simply defining the range as (A1:A15) I need to express my
> range as (A1: "A1+the value in another cell, A20).
>
> Please could you let me know how to achieve this.
> Thanks!
> Jag
>
>
> --
> jagbabbra
> ------------------------------------------------------------------------
> jagbabbra's Profile:
> http://www.excelforum.com/member.php...o&userid=32525
> View this thread:
> http://www.excelforum.com/showthread...hreadid=548517
>

thank you for the suggestion but this seems to only work if you refer to a cell that has an address in ... eg if you use indirect the cell you refer to must contain (A11) or something to that effect.
I have a cell that contains a number and would like to use that to define how many columns the range must cover.Eg If i start my range at B2 and my reference cell (lets call that A20) has the number 3 in then my range should be B2 : D2

Please can you shed some light on this, cheers

Hi

With a value of 15 in cell A20, the range would be A1:A15 and the
formula
=SUM(INDIRECT("A1:A"&A20))
for example would sum all of the values within this range. From your
first description, it sounded as though this was what you wanted..

From what you now describe you could use
=OFFSET(B2,,,1,A20)
as this would refer to the range B2:B4

--
Regards

Roger Govier


"jagbabbra" <[email protected]>
wrote in message
news:[email protected]...
>
> thank you for the suggestion but this seems to only work if you refer
> to
> a cell that has an address in ... eg if you use indirect the cell you
> refer to must contain (A11) or something to that effect.
> I have a cell that contains a number and would like to use that to
> define how many columns the range must cover.Eg If i start my range at
> B2 and my reference cell (lets call that A20) has the number 3 in then
> my range should be B2 : D2
>
> Please can you shed some light on this, cheers
>
>
> --
> jagbabbra
> ------------------------------------------------------------------------
> jagbabbra's Profile:
> http://www.excelforum.com/member.php...o&userid=32525
> View this thread:
> http://www.excelforum.com/showthread...hreadid=548517
>

B2:INDEX(B2:IV2,A20)

jagbabbra wrote:
> thank you for the suggestion but this seems to only work if you refer to
> a cell that has an address in ... eg if you use indirect the cell you
> refer to must contain (A11) or something to that effect.
> I have a cell that contains a number and would like to use that to
> define how many columns the range must cover.Eg If i start my range at
> B2 and my reference cell (lets call that A20) has the number 3 in then
> my range should be B2 : D2
>
> Please can you shed some light on this, cheers
>
>
> --
> jagbabbra
> ------------------------------------------------------------------------
> jagbabbra's Profile: http://www.excelforum.com/member.php...o&userid=32525
> View this thread: http://www.excelforum.com/showthread...hreadid=548517

Much nicer non-volatile solution!

--
Regards

Roger Govier


<[email protected]> wrote in message
news:[email protected]...
> B2:INDEX(B2:IV2,A20)
>
> jagbabbra wrote:
>> thank you for the suggestion but this seems to only work if you refer
>> to
>> a cell that has an address in ... eg if you use indirect the cell you
>> refer to must contain (A11) or something to that effect.
>> I have a cell that contains a number and would like to use that to
>> define how many columns the range must cover.Eg If i start my range
>> at
>> B2 and my reference cell (lets call that A20) has the number 3 in
>> then
>> my range should be B2 : D2
>>
>> Please can you shed some light on this, cheers
>>
>>
>> --
>> jagbabbra
>> ------------------------------------------------------------------------
>> jagbabbra's Profile:
>> http://www.excelforum.com/member.php...o&userid=32525
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=548517
>