Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
2  
The whole point of my first paragraph was to explain the background, including the reason why I can't open a second instance of Excel. –  CJM Feb 21 '12 at 11:10
    
Sorry, misread. Look at that problem - you should easily be able to link workbooks regardless if they're open/closed/in a different Excel instance. There really isn't an easy solution to spanning the Excel instance over both displays... –  HaydnWVN Feb 21 '12 at 11:19
    
Unfortunately, when I initially could not create the cross-workbook links, I did my homework, and the only solutions I found, insisted that I had to open the 2nd workbook, within the same Excel instance. I haven't found an alternative way of creating the necessary links. –  CJM Feb 21 '12 at 11:28
1  
It may be that we can't have our proverbial 'cake and eat it' - But I just didn't want to give up without a fight. –  CJM Feb 21 '12 at 11:30
    
The fight we seem to have lost is asking microsoft to implement multiple windows in single instances (you know, like word.) Excel 2010 is still MDI. –  mtone Feb 22 '12 at 22:26
add comment

2 Answers

up vote 3 down vote accepted

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

wizardry!

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

share|improve this answer
    
My colleague is just going to grin an bear it, but out of the two solutions, yours is the easiest to implement - so you win the laurels...! –  CJM Apr 23 '12 at 15:21
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.