Linking worksheets in Excel

[Guest]

I update my costs using linked worksheet. Chairs have 3 or more parts that
lead to the final cost. When I open the worksheet for the finished chair,
costs are not automatically updated from the linked worksheets for the Back,
Seat and Frame. I need to open these sheets manually, in order for the
updates to flow to the finished Chair sheet. Is there some way to have all
linked sheets open when the finished "Chair" sheet opens, so that all the
cost updates flow to that worksheet? Thanks.

 

[John James]

Edit-Links-Update Values

 

[Guest]

I have tried Edit-Links-Update Values, and the updated values do not appear
on the worksheet. The only thing that happens is the Status changes from
Unknown to OK. The values DO change when the source worksheet is opened--is
there any way to set preferences to OPEN source worksheets when a destination
worksheet is opened? Thanks again.

 

[Guest]

One more thing I could mention--the source worksheets have linked cells to
other source worksheets. So the flow of updated costs could be required to
go through 2 or 3 worksheets to get to the end worksheet.

 

[John James]

Aha. You've answered your own question. The link is working correctly
so that the target workbook is correctly updating with the source
workbook value, but that source workbook has not been updated with its
own source workbook values (or even further down the line) because they
have not been opened.

Each generation needs to be updated before the next generation can be
updated.

A macro like this should open multiple files, updating links:

Sub OpenGrpFiles()
Workbooks.Open Filename:="C:\...\Book1.xls", UpdateLinks:=3
Workbooks.Open Filename:="C:\...\Book2..xls", UpdateLinks:=3
Workbooks.Open Filename:="C:\...\Book3..xls", UpdateLinks:=3
End Sub

You can copy this into a module, adjust it and then attach the macro,
say, to a button.