Excel: Using Functions that Return References

ID: Q81201

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0
Microsoft Excel for OS/2, versions 2.2, 3.0
Microsoft Excel for the Macintosh, versions 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:
1. A1: TEST
2. A2: R4C1
3. A3: TRUE
4. 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.

Additional query words: last error

Keywords :
Version : MACINTOSH:2.x,3.0,4.0; WINDOWS:2.x,3.0,4.0; :2.2,3.0
Platform : MACINTOSH WINDOWS
Issue type :