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