xlf | xlfAddNameRelative


1. Add a series of relative offset names to the Name Manager


A relative offset defined name can only be created (added) in Excel with the Name Manager.


This module describes the features of a relative offset defined name (range name), and also provides some VBA code to automate the new name creation process. The Name Manger dialog box in figure 1 shows three relative offset names, OneLeft, TwoLeft, and OneUp. These names are all with respect to the ActiveCell, or the cell formulas where they are used as references.


In A1 reference style, the Refers To value only makes sense if you know the location of the Active Cell. In figure 1, OneLeft Refers To =!I12. This means that the Active Cell is one column to the right at cell J12. The ! mark means any worksheet, as distinct from a specific worksheet such as Sheet1!


xlf-add-name-relative
Fig 1: the Name Manager dialog box showing the three names, OneLeft, TwoLeft, and OneUp. The Refers To value can only be interpreted by knowing the current location of the Active Cell, namely cell J12

Application: As an example, OneLeft has been used in worksheet in figure 2 (Excel Web App #1). Cell C3: =OneLeft^2, this is equivalent to C3: = B3^2, or R3C3: =RC[-1]^2 in R1C1 reference style. It should be obvious that OneLeft can be used in the context of any cell on any worksheet, with 31 instances in figure 1. There are potentially as many individual (such as OneLeft in this example) relative offset names as there are cells in a workbook. The maximum number of defined names is limited only by available memory.


Fig 2: Excel Web App #1 - each formula in column C (range C3:C12) returns the square of the constant in column B. For example, C3: = OneLeft ^ 2. A second example starting at row 15, creates a vector of discount factors

Using relative offset names


Relative offset names do not appear in the Name Box list, but are available from:


Although not available in the Active Cell list, the user can type the relative offset name in the Name Box. Suppose that the Active Cell is cell A1. If the user types OneLeft in the Name Box then the worksheet will scroll to cell XFD1. Equivalent to going left from the first cell, off the page and ending up on the last cell. Similarly, if the Active Cell is in any cell in row 1, say L1, then typing OneUp in the Name Box will scroll to cell L1048576 (over the top and down to the bottom).


2. xlfAddNameRelative - the VBA code


Relative offset names cannot be readily copied between workbooks, especially if Comments are used. The following macro, xlfAddNameRelative provides an easy way to setup the name structure (code 1).



Code 1: Sub procedure xlfAddNameRelative adds relative offset names to the workbook Names object (appearing in the Name Manager) 
Private Sub xlfAddNameRelative()

With ActiveWorkbook
        .Names.Add Name:="OneLeft", RefersToR1C1:="=!RC[-1]"
        .Names("OneLeft").Comment = "Cell one column left (relative)"
        .Names.Add Name:="TwoLeft", RefersToR1C1:="=!RC[-2]"
        .Names("TwoLeft").Comment = "Cell two columns left (relative)"
        .Names.Add Name:="OneUp", RefersToR1C1:="=!R[-1]C"
        .Names("OneUp").Comment = "Cell one row up (relative)"
End With

End Sub

Names are added in R1C1 style thus avoiding the problem of knowing the location of the Active Cell. Run the private procedure from the VBE. Use F5 or Run > Run Sub on the menu.