Linking to an external workbook

[haz115]

I'm trying to link a cell to a different cell in another workbook, however cannot change the absolute reference of the filename in the formula, e.g =[Book2]Sheet1!$B!2.

Is it possible to use a cell reference (i.e A1) in the square brackets in the above formula? I keep getting error messages due to excel looking for the file A1.xls

[datagopher]

Good Afternoon!

The square brackets are for the workbook name so if you put in "A1", Excel will be looking for a workbook named "A1".

If you are entering the formula into the cell directly you need to add the file path of the target workbook into your formula. Like this:

='Target workbook file path\[Target Workbook Name.xls]Target Sheet Name'!$B$2

If you have both workbooks open in the same session you can enter "=" into the cell you are working on, switch to the other workbook with "Window", select the target cell, return to the original workbook, and hit enter. The file path won't be there but when you close the other workbook it will place it for you. Or just type it in manually if you know the address of your target cell.

If you are coding with VBA in an event or macro you can reference a workbook name entered in a cell and place it into the code. Like this:

Dim BookName As String
BookName = "Target Workbook Name.xls"
' or if your workbook name is entered into a cell with the xls extension
BookName = Range("B3").Text
' or if your workbook name is entered into a cell without the xls extension
BookName = Range("B3").Text & ".xls"

Range("A1").Formula = "='Target workbook file path\[" & BookName & "]Target Sheet Name'!$B$2"

I hope this helps!