DataWright Information Services
Consulting and Resources for Excel and Access
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.
