cell range setting

[luffyfang]

Hi

I want to know when setting up a cell range say A2:A100.

Is it possible to set something like A2:A(reference to another cell that contain a number i want) or A2:A(include a formula here which give me the desired number)

Many thanks

 

[sericom]

Couple of examples

INDIRECT("A2:A"&B2)
INDIRECT("A2:A"&B2/2)

 

[Rick Rothstein]

Hi

I want to know when setting up a cell range say A2:A100.

Is it possible to set something like A2:A(reference to another cell that contain a number i want) or A2:A(include a formula here which give me the desired number)

There are two ways. Let's say that cell B1 contains 100. This first way uses the INDIRECT function which is Volatile...

INDIRECT("A2:A"&B1)

and this way which is not Volatile...

INDEX(A:A,2):INDEX(A:A,B1)