Excel: Using Functions that Return References

Last reviewed: September 2, 1997
Article ID: Q81201

2.x 3.00 4.00 | 2.20 2.21 3.00 | 2.x 3.00 4.00

WINDOWS       | OS/2           | MACINTOSH

kbprg

The information in this article applies to:

Microsoft Excel for Windows, version 2.x, 3.0, 4.0
Microsoft Excel for OS/2, version 2.2, 3.0
Microsoft Excel for the Macintosh, version 2.x, 3.0, 4.0

SUMMARY

The "Microsoft Excel Function Reference" describes many functions as returning a reference. These include ABSREF, ACTIVE.CELL, INDIRECT, LAST.ERROR, OFFSET, SELECTION, and TEXTREF. When functions are entered by themselves into a cell, the value of the cell will be the value contained in the reference returned by the function, not the reference itself. This is because the value of a cell must be a number, text, logical value, or error value; a reference is not a "value."

MORE INFORMATION

The "Microsoft Excel Function Reference" documents that reference-returning functions display the value contained in the reference. Although the value is displayed, the reference is still returned.

Example

A good way to see a reference returned by a function is to use the REFTEXT function to convert the reference to a text string.

Enter the following onto a macro sheet:

      A1: =OFFSET(A1,3,0)
      A2: =REFTEXT(OFFSET(A1,3,0))
      A3: =RETURN()
      A4: TEST

Select cell A1 on the macro sheet.
From the Macro menu, choose Run. Choose OK.
Press CTRL+LEFT QUOTATION MARK (`) to make the sheet display values. The values should show as follows:
```
      A1: TEST
      A2: R4C1
      A3: TRUE
      A4: TEST
```

While the OFFSET in cell A1 appears to have only returned the text value in A4 and not the reference, the REFTEXT function in A2 shows that the OFFSET did indeed return the correct reference.

REFERENCES

"Microsoft Excel Function Reference," for Windows, version 3.0, pages xiii, 1, 3, 137, 163, 214, 235.

KBCategory: kbprg
KBSubcategory:
Additional reference words: 3.00 4.00 last error
Keywords : PgmOthr

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 2, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.