You can accomplish this using `INDIRECT`

and `OFFSET`

functions.

To give you an example, let's assume that you have just one column of five data points, with the first row being the header and second row onward being the data. Now you want to sum the column up.

```
|| A
==||======
1 || Sale
2 || 1
3 || 2
4 || 3
5 || 4
6 || 5
```

and you want `SUM`

from the first value to the last value always. For a fixed set like the one shown above, you could just do `=SUM(A2:A6)`

in the `A7`

cell.

But you want to be able to add rows to the top. And you expect the function to be able to sum from the first data row to the last data row just before where the formula is used. So you would use `INDIRECT("A2")`

as the starting index (A2 doesn't change even if you add a new row), and `OFFSET(A7, -1, 0, 1, 1)`

as the last index (A7 will change to A8 if a new row is added, and the offset will make sure that you're still summing until one cell above A8).

```
=SUM(INDIRECT("A2"):OFFSET(A7,-1,0,1,1))
```

Now, even if you add a new row at the top, your formula will work just fine.

I hope this helps.