How to Use Indirect in Excel to Link to an External Spreadsheet

Written by ken burnside Google
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Use Indirect in Excel to Link to an External Spreadsheet
Excel's INDIRECT function lets you dynamically pull data from external sources. (calculation image by lefty from

Excel's INDIRECT function allows you to substitute the names of named ranges and have them evaluate when the formula is read. INDIRECT can also be used to change the name of a spreadsheet from which Excel pulls external data. This can be useful if you are building a summary page from many spreadsheets that are configured identically but have different names. For example, you could build a summary page of sales pages where each sales agent enters his sales on his worksheet. These data are then put up on a server, and the summary page then can pull data from each worksheet.

Skill level:

Things you need

  • Excel 1995 or later

Show MoreHide


  1. 1

    Create two spreadsheets. One will be named PullData.xlsx. The other will be ReviewData.xlsx.

  2. 2

    Enter the following into cell A1 of Sheet 1 of PullData.xlsx: "Shazam!"

  3. 3

    Enter the following formula into cell A2 of PullData.xlsx: "=27+54"

  4. 4

    Switch to ReviewData.xlsx. Enter the following text into the following cells: In cell B1, enter "PullData.xslx". In cell B2, enter "Sheet1". In cell B3, enter "A1" and in cell B4, enter "A2".

  5. 5

    Enter the following formula in cell A1 on ReviewData.xlsx: "=INDIRECT("'["&B1&"]"&B2&"'!"&B3)". Note that there are both double quotes (") and single quotes (') in that formula; they're there to ensure that the formula works even if the file name or the sheet name inside the file have spaces.

  6. 6

    Hit Enter. Cell A1 in ReviewData.xlsx should now say "Shazam!" If you change the &B3 in the last part of the INDIRECT formula to &B4, cell A1 ReviewData.xlsx will show "81".

Tips and warnings

  • Both spreadsheets must be open for the INDIRECT function to work. If the PullData.xlsx file is not open, the INDIRECT function will return #REF! as its result. A workaround that lets an INDIRECT-type function pull data from a closed workbook can be found by using a third-party formula extension for Excel, called INDIRECT.EXT. Care should be taken when using it, as it will slow down Excel considerably.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.