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).

The first table, before adding a new row

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.

Adding a row causes errors

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.

Using the range name, errors are removed

By doing this you have removed a potential source of error in
your spreadsheet models.