msgbartop
Just another WordPress site
msgbarbottom

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.