DISCOVER
×

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

Updated April 17, 2017

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.

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

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

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

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".

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.

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".

Warning

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.

Things You'll Need

  • Excel 1995 or later
bibliography-icon icon for annotation tool Cite this Article

About the Author

Ken Burnside has been writing freelance since 1990, contributing to publications as diverse as "Pyramid" and "Training & Simulations Journal." A Microsoft MVP in Excel, he holds a Bachelor of Arts in English from the University of Alaska. He won the Origins Award for Attack Vector: Tactical, a board game about space combat.