Excel: Incorrect Usage of the TEXTREF Function May Return #REF

SUMMARY

The TEXTREF function was improved in Microsoft Excel version 3.0 to allow the text argument to be specified as a general reference rather than as a full external reference. In versions prior to 3.0, the text argument in the TEXTREF function was required to be stated as a full external reference when it referred to cells off the macro sheet. Therefore, in versions prior to 3.0, referring to cell locations located anywhere other than on the macro sheet that the TEXTREF function is resident on without specifying the worksheet name in the reference will cause the error #REF.

MORE INFORMATION

For example, if the TEXTREF function is being used to return the value in cell B2 of the active worksheet, where B2 equals 5, the following is true:

1. In all versions of Excel up to and including version 3.0, the formula TEXTREF("'Sheet1'!r2c2",FALSE) would return the number 5. However, since this formula uses a full external reference, Excel will look only at cell B2 on Sheet1. To work around this limitation, you can use TEXTREF("'"&GET.DOCUMENT(1)&"'!r2c2",FALSE), where GET.DOCUMENT returns the name of the currently active document.

2. In Excel 3.0, the formula TEXTREF("!r2c2",FALSE) would also return 5. However, since this formula is entered as a general reference, Excel will look at cell B2 on the active worksheet. In versions prior to 3.0, this formula would return #REF and therefore require the complete external reference as in 1 above.

For more information on the TEXTREF function, see pages 235-236 of the "Microsoft Excel Function Reference" version 3.0 manual or page 288 of the "Microsoft Excel Functions and Macros" version 2.2 manual.