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!
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.
Using relative offset names
Relative offset names do not appear in the Name Box list, but are available from:
- F3 Paste Name shortcut
- The Defined Names > Use in Formula list, and
- The Formula AutoComplete list
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
xlfAddNameRelativeadds 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.
- This example was developed in Excel 2016 Pro 64 bit.
- Published: 10 August 2016
- Revised: Wednesday 18th of July 2018 - 09:43 AM, [Australian Eastern Standard Time (EST)]