View Full Version : workbook link ref cell
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
12-03-2011, 11:17 AM
In 123.xls enter the source cell
='C:\Users\MD\Documents\[Source.xls]Sheet1'!$A$1
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
12-04-2011, 08:47 AM
Use =Indirect to build the links. If you want to change the formulas by VBA, we can look at doing that.
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")
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
12-04-2011, 01:07 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.