- In an open file named TestFile.xls, on a worksheet named Test Data, enter numbers in cells A1:A10
- On a sheet in a different workbook, in cell A2, enter the file name:
- In cell A3, enter the sheet name: Test Data
- In cell A4, type a cell name from the range of numbers, e.g.: A7
- To see the syntax that you'll need to use in your formula, select cell A7, and type an equal sign: =
- Switch to Test File.xls, and click on cell A7
- In the formula bar, you'll see the reference that was created:
='[Test File.xls]Test Data'!$A$7
- You'll create an INDIRECT formula that uses the same syntax, adding the single quote marks, square brackets and apostrophe.
- In cell A6, type the following formula:
=INDIRECT("'[" & A2 & "]" & A3 & "'!" & A4)
- The first part of the string is a single quote and square bracket, within a set of double quotes:
" ' [ " (spaces were added for clarity)
- After the reference to A2, which contains the file name, is a square bracket, within a set of double quotes:
" ] " (spaces were added for clarity)
- After the reference to A3, which contains the sheet name, is a single quote and apostrophe, within a set of double quotes:
" ' ! " (spaces were added for clarity)
- The string ends with a reference to cell A4, which contains the cell address.
- Single quotes are included in the string to prevent errors if the sheet name contains space characters.
- Press the Enter key, and the formula returns the number in cell A7 on the Test Data worksheet, in the Test File.xls workbook.
- Change the values in cells A2:A5, and the result in A6 will change.
- You can delete the sample link in cell A5
Note1: If A2, A3 or A4 is empty, the formula will return an error. To prevent this, you can add an IF function:
=IF(OR(A2="",A3="",A4=""),"",INDIRECT("'[" & A2 & "]" & A3 & "'!" & A4))
Also, you can use INDIRECT.EXT, this function works as INDIRECT but in addition to that it also works for closed workbooks.
Hope this helps you.
Do update us again,