Creating an array of numbers of certain length like {1;2;3;4;5} on a worksheet is something that comes up occasionally. But getting a perfect formula to create an array fill that’s not affected by worksheet changes can be a little bit tricky. However, a technique based on ROW and INDIRECT functions precisely what you need. This formula excels where numeric arrays are needed.

**Generic Formula**

`{=ROW (INDIRECT (start & “:” & end))}`

……where the “start” is the 1st number and the “end” is the last number in the array.

**Example**

If you want to want to get create arrays of numbers, here is an example of how this formula. Can be helpful:

*Figure 1. Example 1 of **ROW/INDIRECT** Functions*

As seen in the ‘*Example1*’, the formula in D6 is:

`=ROW (INDIRECT (B6 & “:” & C5))`

…the formula creates array of numbers like {1;2;3;4;5;6;7}.

**This is How the Formula Works**

In this example, the formula picks the “start” and “end” numbers from B6 and C6 respectively. This is how the breaks down the values:

` =ROW (INDIRECT (B6 & “:” & C6))`

`=ROW (INDIRECT (1 & “:” & 5))`

`=ROW (INDIRECT (“1:5”))`

`=ROW (1:5)`

`= {1; 2; 3; 4; 5}`

The INDIRECT function guards against worksheet changes and thus any changes in the row either by deleting or inserting new rows not alter the range reference. Here is an example of would happen:

` =ROW (1:5)`

Given the above, it will change to this if a row is deleted:

`=ROW (1:4)`

That is because the INDIRECT functions work with a reference that is constructed with text which is altered by changes on the worksheet.

## Leave a Comment