Using relative names
In most cases, range names are used as absolute references. But,
there are times when a relative (or partial) reference can be
useful. Here are some examples:
A name that always refers to the cell
above the current cell
When adding new rows to a spreadsheet model, you need to be
careful about adding rows to a series with a total or subtotal. For
example, in this sales summary the total in C6 is =SUM(C3:C5).
When you add a new row, if you add it below Row 5 you will get an
error. The data in Row 6 will not be part of the calculation, and
you will get the wrong result. In later versions of Excel, you will
see warnings in the cells to indicate that your sum does not cover
all rows.
If you regularly change your layout like this, you may forget to
update the ranges and your model will be incorrect. One option is to
create a relative range name that always references the cell above;
we’ll call it AboveMe. First define the range name.
Select A6 on the current worksheet, and:
In Excel 2007
- Formulas > Name Manager, and click the New button in the
dialog. - In the Name row, type AboveMe
- In the RefersTo row, leave the sheet reference as it is but
change the cell reference from $A$6 to A5. - Click OK
In versions up to 2003
- Insert > Name > Define
- In the Name row, type AboveMe
- In the RefersTo row, leave the sheet reference as it is but
change the cell reference from $A$6 to A5. - Click OK
On the spreadsheet, select the formula in C6. Change it to
=SUM(C2:AboveMe), then fill right. Now, if you add a row and insert
data, the range automatically adjusts to accommodate the new row.
By doing this you have removed a potential source of error in
your spreadsheet models.