Excel: Concatenating a Link Formula Using INDIRECT()

 Last reviewed: April 26, 1995
Article ID: Q57393

The information in this article applies to:

  • Microsoft Excel for the Macintosh, versions 1.5, 2.2, 3.0, 4.0, 5.0, 5.0a

The INDIRECT() function can be used to link to open files in Microsoft Excel. The advantage to using this function is that you can include both the filename and the cell that contains the information in a formula. For example, if the following are true

  • Cell A1 contains the filename

    -and-

  • Cell A2 contains the a reference to the cell that you want to link to as text (that is, $A$1)

the formula to link to an open worksheet having only the filename as text in cell A1 is as follows 

   =INDIRECT("'"&A1&"'!<ref>")
where <ref> is the cell reference of the desired data.

The formula to link to an open worksheet having the filename in cell A1 and the cell to link to in cell A2 is as follows: 

   =INDIRECT("'"&A1&"'!"&A2&"")
NOTE: The above formulas will only return a value if the worksheet being linked to is open; otherwise, the formulas will return a #REF! error value.

KBCategory: kbother
KBSubcategory: xlmac

Additional reference words: 1.50 2.20 3.00 4.00 5.00

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: April 26, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.