16 Feb 09 Relative range names in practice

With range names you can do big tricks with excel.

I will show you one of these tricks today:

When you define a range name, then excel will propose the absolute cell reference as default.
But you can use a relative or a mixed reference too.

The active cell is the starting point.
You have to create your reference like you would write into that cell.

My trick is:

1. Select A2 cell.
2. Choose Insert –> Name –> Define
3. Type a name for the range: “Last”
4. Write in the refers to box “=A1″ without quotes and without dollar signs.
5. Give a name to this range, for example “last”

This range name will refer to the upper cell to the current position.

It can be used like this:

=SUM(B2:Last)

will give you a dynamic range that will grow or shrink when you insert or delete some rows, even if the deleted row is the last row from the range.
This is a perfect solution.

If you insert multiple rows and don’t fill every cell, the normal reference will not upgrade, the relative name will always work.