Defining a range using a cell reference

[jagbabbra]

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 m
range as (A1: "A1+the value in another cell, A20).

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

 

[Roger Govier]

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

--
Regards

Roger Govier


"jagbabbra" <[email protected]>
wrote in message
news:[email protected]...

 

[jagbabbra]

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

 

[Roger Govier]

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]...

 

[akyurek]

B2:INDEX(B2:IV2,A20)

 

[Roger Govier]

Much nicer non-volatile solution!