Notes Old site

Using Names for formulae

The usual way to use Names in Excel is to use them to refer to absolute ranges. Typically, the name VATRate might refer to the range $D$2, for example.

However, it is possible to define names to refer to formulae that use relative ranges.

Names that refer to formulae can be useful when they are used to define dynamic ranges. On the whole, however, they should be used with caution: make sure they are documented somewhere, so that a future maintainer will find it easy to understand what is going on. One source of confusion is that their definition may depend on the currently active cell.

Names for formulae

When you define a name using the Define Name dialog, you can actually type any formula you like in the Refers to box. The formula is interpreted as being relative to the currently active cell in the workbook.

For example, if you have cell B9 selected, so that it is currently active, you could define a name AboveAndLeft as referring to =B8+A9. You could then use this name in cell D12, say, to add the
values in D11 and C12.

Picture of formula name