Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Question about link
I have one master workbook and several sub-workbooks. All the sub-workbooks
get assumptions from the master workbook and return the result to master workbook. Is there a way to automatically update all the sub-workbooks after I change some assumptions in the master workbook and return the new result to master workbook? I've tried Edit/Link/Update now in the master workbook after I changed the assumptions, it doesn't work. Any help will be greatly appreciated. Jason |
#2
|
|||
|
|||
When you open those subworkbooks, you'll be prompted (if you use that setting)
to see if you want to update the links. So you could open, update the links and save/close. (Or just wait until you really want to use that subworkbook.) Jason wrote: I have one master workbook and several sub-workbooks. All the sub-workbooks get assumptions from the master workbook and return the result to master workbook. Is there a way to automatically update all the sub-workbooks after I change some assumptions in the master workbook and return the new result to master workbook? I've tried Edit/Link/Update now in the master workbook after I changed the assumptions, it doesn't work. Any help will be greatly appreciated. Jason -- Dave Peterson |
#3
|
|||
|
|||
Links are not bi-directional. You would need to create a link from Wbk A to B
and a separate link from B to A. (It sounds like that is already the case, just needs to be expanded on.) Not sure, but it is possible you may wind up with circular references if you overdo it. |:) "Jason" wrote: I have one master workbook and several sub-workbooks. All the sub-workbooks get assumptions from the master workbook and return the result to master workbook. Is there a way to automatically update all the sub-workbooks after I change some assumptions in the master workbook and return the new result to master workbook? I've tried Edit/Link/Update now in the master workbook after I changed the assumptions, it doesn't work. Any help will be greatly appreciated. Jason |
#4
|
|||
|
|||
Dave,
Is there a way to update the link without opening the subworkbooks. Not mean to be lazy, about 30 subworkbooks supporting the master workbook, and am trying to do some scenario analysis. Thanks a lot. Jason "Dave Peterson" wrote: When you open those subworkbooks, you'll be prompted (if you use that setting) to see if you want to update the links. So you could open, update the links and save/close. (Or just wait until you really want to use that subworkbook.) Jason wrote: I have one master workbook and several sub-workbooks. All the sub-workbooks get assumptions from the master workbook and return the result to master workbook. Is there a way to automatically update all the sub-workbooks after I change some assumptions in the master workbook and return the new result to master workbook? I've tried Edit/Link/Update now in the master workbook after I changed the assumptions, it doesn't work. Any help will be greatly appreciated. Jason -- Dave Peterson |
#5
|
|||
|
|||
The subworkbooks get that information from the parent workbook?
And you want to retrieve that same information from the subworkbooks???? I think that the workbooks have to be opened. But you could open them with a macro and then save/close them. I built a list of the workbook names in A2:A31 (about) and ran this macro: Option Explicit Sub testme01() Dim myRng As Range Dim myCell As Range Dim TempWkbk As Workbook Dim testStr As String With Worksheets("sheet1") 'headers in row 1 Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In myRng.Cells testStr = "" On Error Resume Next testStr = Dir(myCell.Value) On Error GoTo 0 If testStr = "" Then MsgBox "typo in: " & myCell.Address(0, 0) Else Set TempWkbk = Workbooks.Open(Filename:=myCell.Value, UpdateLinks:=1) TempWkbk.Close savechanges:=True End If Next myCell End Sub Ah....Maybe the subworkbooks do a lot of calculation with that information that's pushed to them via the links and you're using the results of that calculation???? Jason wrote: Dave, Is there a way to update the link without opening the subworkbooks. Not mean to be lazy, about 30 subworkbooks supporting the master workbook, and am trying to do some scenario analysis. Thanks a lot. Jason "Dave Peterson" wrote: When you open those subworkbooks, you'll be prompted (if you use that setting) to see if you want to update the links. So you could open, update the links and save/close. (Or just wait until you really want to use that subworkbook.) Jason wrote: I have one master workbook and several sub-workbooks. All the sub-workbooks get assumptions from the master workbook and return the result to master workbook. Is there a way to automatically update all the sub-workbooks after I change some assumptions in the master workbook and return the new result to master workbook? I've tried Edit/Link/Update now in the master workbook after I changed the assumptions, it doesn't work. Any help will be greatly appreciated. Jason -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question about link | Excel Discussion (Misc queries) | |||
Link question.... | Excel Discussion (Misc queries) | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) | |||
Link Question | Excel Worksheet Functions | |||
How do link to a remote field but use the path from a stored field | Excel Discussion (Misc queries) |