ROW and INDIRECT

[Ufoo]

Hello gurus,

I have managed to generate an array of numbers using ROW(A3:A10)-ROW(A3)+1, and if there is a number in cell H9; ROW(INDIRECT("1:"&H9)). Is there a way of generating an array of numbers using ROW and INDIRECT if there isn't a number in cell H9? Just wondering aloud. Thanks.

 

[NARAYANK991]

Hi ,

To generate an array of numbers , a start point and an end point are needed.

To simplify matters , we can assume a default start point of 1.

There is no way we can simplify matters as far as the end point is concerned.

If you do not wish to specify the end point , you can always hard code the number of elements ; thus , if you want an array of 15 elements , to start from the default starting point of 1 , and going up to 15 , as follows :

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}

then the following formula can be used :

=ROW(OFFSET($A$1,,,15))

Narayan