workbook link ref cell

[wilg]

How can I substitute a cell ref inside a formula in a cell that is a link to another workbook?

Eg.

C: /my documents/123.xls

I want to be able to change the 123.xls by keying in a cell the workbook I want to ref to.

[mdmackillop]

In 123.xls enter the source cell
='C:\Users\MD\Documents\[Source.xls]Sheet1'!$A$1

[wilg]

Hi, I'm not sure if I explained properly maybe, entering the source cell does not seem to work.

='C:\Users\MD\Documents\[b2.xls]Sheet1'!$A$1

it gives an "update values" prompting a new window to open and asking for a file name

In my master workbook I have already linked the source workbook. But If want to change that source workbook to another workbook, I want to change it to ref the title of the bokbook by keying in the name into a cell in my master workbook.

eg if I key "123" into cell A3, then I want my linkes to find that workbook and ref thoes cells. If I key "456", then I want to my links to ref thoes cells in that workbook. All of which is stored in the same file.

[Kenneth Hobs]

Use =Indirect to build the links. If you want to change the formulas by VBA, we can look at doing that.

[wilg]

Ok, I managed to now learn indirect and it works with an open workbook, but not a closed workbook to which this would be ref to.

I've read through Harlan Grove's Pull function and am now trying to use this to do what I want but with little success.

Are you familiar with this function, and if so how do I put the correct formula for pull to make it work based on the following indirect that works..

=INDIRECT("'c:\Users\MD\Documents\["&$B$2&".xls]Sheet1'!$A$2")

[wilg]

ok, well sometimes the answer is right infrom of me. By placing pull infront aloge with Harlan Grove's pull function in a module I get my result from a closed workbook.

=pull("'c:\Users\MD\Documents\["&$B$2&".xls]Sheet1'!$A$2")

Now trying to copy across the formula in the worksheet is proving a problem as the cell it ref to "A2" stays locked and will not change to "B2" when I copy it across...any thoughts?

I've removes the $ and still does not change...

[Kenneth Hobs]

I separate the parameters.

Sub t()
MsgBox GetValue("x:\test", "test.xlsx", "Sheet1", "A1")
End Sub

'=GetValue("c:\files", "budget.xls", "Sheet1", "A1")
Private Function GetValue(path, file, sheet, ref)
' path = "d:\files"
' file = "budget.xls"
' sheet = "Sheet1"
' ref = "A1:R30"

Dim arg As String

If Right(path, 1) <> "\" Then path = path & "\"

If Dir(path & file) = "" Then
GetValue = "file not found"
Exit Function
End If

arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("a1").Address(, , xlR1C1)

GetValue = ExecuteExcel4Macro(arg)
End Function