View linked Excel 'Windows' on separate screens

Question

A colleague had a problem where he couldn't link a cell in his destination workbook with a series of cells in a source workbook. The problem was that in order to link workbooks, he had to open them from within the same Excel instance. If he double-clicked on the destination workbook, or fired up Excel independently of the first, the intra-book linking didn't work.

Now he has this solution, he as a different problem. He wants to be able to view and manipulate both source and destination workbooks at the same time - on different screens. However, because both workbooks are now running withing the same instance of Excel, when he drags one workbook across to his second monitor, the first is carried across too.

Is there a way he can keep these workbooks linked, but view them on separate screens?

Answer 1

Yes, the crude way to do this is to re-size excel to span both screens and then re-size each workbook independently and have each sit in the area of excel on each screen

It appears that he was on the right track, but he needs to use the restore down button for excel, re-size it and then manually re-size each workbook. If the workbooks are maximized, click the restore window button near the top right of excel to independently re size the workbook windows

---

Edit - you can also expand excel across both screens and go to view - arrange all - vertical

Answer 2

Once you have created the link and saved the files then the link is still there when you open the workbooks up in two different instances of Excel. I'm guessing the real problem is that your colleague wanted his changes in one workbook to automatically show up in the other file.

The problem is that, in general, one instance of Excel cannot see what is happening in another instance of Excel.[1] So when the two linked files are opened in two separate Excel instances, the only way they are able to know about changes in the linked file is by looking at the file on disk.

If you want file 2 to automatically update whenever file 1 changes and at the same time view the two workbooks two separate screens, then follow Raystafarian's answer. If you really want two separate instances of Excel then you have to do a couple extra steps.

1. Change file 1.
2. Save the change.
3. Switch to file 2.
4. Update the link.

To update a link manually in Excel 2007 select Data tab -> Edit Links -> Update Values. Alternatively you could add a quick macro to file 2's ThisWorkbook module.

Public Sub UpdateMyLinks()
   ThisWorkbook.UpdateLink ThisWorkbook.LinkSources
End Sub

Assign the macro to a keyboard shortcut and it becomes a lot easier to do the last step.

[1] You could write a VBA macro to connect them but it probably isn't worth the effort.