Thursday, February 14, 2013

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


1. Create two spreadsheets. One will be named PullData.xlsx. The other will be ReviewData.xlsx.
2. Enter the following into cell A1 of Sheet1 of PullData.xlsx: 'Shazam!'
3. Enter the following formula into cell A2 of PullData.xlsx: '=27 54'
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. 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. 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'.

Blogger news