Excel: OFFSET Returns a Reference, Not an Action Command

ID: Q59621

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0
Microsoft Excel for the Macintosh, versions 1.x, 2.x, 3.0, 4.0

SUMMARY

In Microsoft Excel, the OFFSET macro function returns a reference based on the entered arguments and does not actually perform an action, such as selecting a group of cells. It can, however, be used as an argument in a macro function that does perform a particular action. For example


   =OFFSET(SELECTION(),1,1)

returns the active external reference (returned by the SELECTION function) shifted by one row and one column. Therefore, if the macro was run with the above statement and the active selection was "A1:C2", the formula would return the reference of "B2:D3".

To actually select the offset of a group of cells, use the OFFSET function as the reference argument in the SELECT function. On a worksheet or macro sheet, using the above example


   =SELECT(OFFSET(SELECTION(),1,1))

selects the active group of cells, shifted down by one row and over to the right by one column.

For more information about OFFSET in Microsoft Excel 4.0, see pages 299-300 in the "Microsoft Excel Function Reference." If you are using Microsoft Excel 3.0, see pages 163-164 in the "Microsoft Excel Function Reference" version 3.0. If you are using Microsoft Excel 2.20, see pages 251-252 in the "Microsoft Excel Functions and Macros" version 2.2.

For more information about SELECT and SELECTION in Microsoft Excel 4.0, see pages 378-381 and 383-384 respectively. If you are using version 3.0, see pages 209-212 and 214, respectively, in the "Microsoft Excel Function Reference," version 3.0. If you are using version 2.2, see pages 274-275 and 278, respectively, in the "Microsoft Excel Functions and Macros," version 2.2.

Additional query words: 1.0 1.00 1.03 1.04 1.06 1.5 1.50 2.2 2.20 3.0

Keywords :
Version :
Platform :
Issue type :