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


--
jagbabbra
------------------------------------------------------------------------
jagbabbra's Profile: http://www.excelforum.com/member.php...o&userid=32525
View this thread: http://www.excelforum.com/showthread...hreadid=548517

Defining a range using a cell reference
 

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

--
Regards

Roger Govier


"jagbabbra"
wrote in message
...

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

Defining a range using a cell reference
 

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

Defining a range using a cell reference
 

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"
wrote in message
...

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

Defining a range using a cell reference
 

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

Defining a range using a cell reference
 

Much nicer non-volatile solution!

--
Regards

Roger Govier


wrote in message
ups.com...
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