Mac Forum / Applications / Excel / October 2007

"relatively"?

Note that by default, Bob's method creates an absolute reference (e.g.,
it always refers to the same cell on a specific sheet). One can also
create a relative referenced named range. For instance, with B1 the
active cell, choose Insert/Name/Define:

   Name in Workbook:   CellToLeft
   Refers to:          =A1

Then whenever the name is used, it will refer to the cell to the left of
the cell it's used in, e.g.,

D1:     =CellToLeft * 2

will multiply C1 by 2 and return the value to D1. If the name had been
entered when D1 was active, CellToLeft would refer to the cell two cells
to the left of the referring cell.

You can also define a reference that is absolute with respect to the
cell address, but relative with respect to the sheet. In the
Insert/Name/Define dialog, enter

   NameInWorkbook:     MyA1Reference
   Refers to:          =!$A$1

then when used on Sheet1, MyA1Reference will refer to Sheet1!A1, but
when used in Sheet3, MyA1Reference will refer to Sheet3!A1.

Note that this technique creates one type of named range - specifically
a range associated with a Workbook level name.

You can also create a Worksheet-level named range by including the name
of the worksheet in the namebox or Insert/Name/Define entry, e.g.,:

   Sheet1!MyName

Then one can create a dynamic named range by entering a formula that
returns a range in the Insert/Name/Define dialog, e.g.:

   Name in workbook    MyDynamicName
   Refers To:          =OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),1)