2 Excel files/windows on 2 different monitors - but still able to Vlookup between them

[Colin Staples]

Hi all,

I'm running Excel 2010 on Windows 7, and I have dual monitors.

I'd like to have one Excel file open on the left monitor, and one open on the right monitor - but here's the important bit : I need them to be in the same instance of Excel so that I can do Vlookups etc between the two files.

Every help article I've found on the internet explains how to open two separate instances of Excel, and that does allow me to have one file on each monitor, but because they are not the same instance it does not allow Vlookups etc between the two.

To have 2 separate instances:
Pin Excel to the toolbar in Windows 7.
Click the toolbar icon to launch instance 1.
Hold ctrl + click the toolbar icon to launch instance 2 (or click the icon with middle mouse button)
They can now be moved to whichever monitor you wish. But you cannot do Vlookup between them.

How do I have two different files on two different screens, and be able to do work between them?

Note that this is a work computer where I do not have admin access, so modifying the registry is not possible (and is very scary, and why should it be necessary anyway?)

I've Googled the heck out of this, and asked the Guru at work, but so far cannot find an answer. Everything keeps coming back to the "two separate instances" method.

Thanks in advance for any help.

* Note that the problem is not just limited to Vlookup, it's all functions and formulas that may go between two files.

 

[cyrilbrd]

Hi, I am curious to see how this will be answered, I am running excel on a MAC (not applicable scenario in your query) on 3 monitors and have no problem whatsoever with formulae getting query from one sheet to the other.
May I ask what do you mean by 'same instance'?
same file opened twice? (not possible I think)
similar files opened from different directories (should work fine)
what formula are you using exactly?
Hopefully you'll get more feedback from now on.

 

[bschwartz]

Same as Cyrilbrd, I'm not familiar with the problem of not being able to reference the other open workbook, however, maybe this will help you:

1) Restore Down Excel (the opposite of maximize - top right next to the red X).
2) Manually resize Excel to the top and bottom of the screen, and drag it all the way across through the second screen. Excel (one worksheet) should now occupy both of your screens.
3) With both workbooks open on your computer, go to the View tab --> Arrange all --> Vertical.

Hope that helps.

 

[Colin Staples]

Thanks very much for your replies.

Firstly, I'd like to clarify exactly what I mean by 'same instance' and 'separate instance' of Excel:

I am running Excel 2010 on Windows 7

* Scenario 1 - Opening 2 workbooks in the same instance:

Start with all Excel windows closed, and Excel not running.
Click on the start menu and launch Excel.
Click on File > Open to open any workbook.
Without leaving that Excel window, click on File > Open again and open a different workbook.
You now have 1 instance of Excel running, with two workbooks open.

Because the two workbooks are in the same instance of Excel, you CAN use formulas to link between them (which is the operational set up I am seeking) but you CANNOT have one workbook on the left monitor, and one workbook on the right monitor (which defeats the visual set up I am seeking).

Note that in this scenario you cannot have the same file open twice - if you try and open the same file a second time you will be told that it is already open.

* Scenario 2 - Opening 2 workbooks in the separate instances:

Start with all Excel windows closed, and Excel not running.
Click on the start menu to launch Excel.
Click on File > Open to open any workbook.
Click on the start menu again to launch Excel again.
Click on File > Open to open a different workbook.
You now have 2 instances of Excel running, with each instance having one workbook open.

When I Googled for this problem, this was the most common method that came up.

Because the two versions of Excel are separate, you CAN have one workbook on the left monitor, and one workbook on the right monitor (which is the visual set up I am seeking) but you CANNOT use formulas to link between them (which defeats the operational set up I am seeking)

Note that in this scenario you can have the same file open twice - once in each of your two instances of Excel. If you try to open the file a second time you will not get the normal 'this file is already open' message, instead you will be able to open the file as 'read only' because Excel/Windows behaves as if the file is already open by another user on your network.

-----

To explain more about the formula issue, imagine that in each of the scenarios described above you have two workbooks open, named "Workbook A" and "Workbook B"

In each of these two workbooks, cell A1 contains the value 5.
In cell A2 of workbook A you wish to add together these two values.

In Scenario 1 (two workbooks open in the same instance of Excel) this is what would happen:
Start in cell A2 of Workbook A.
Start the formula by typing =
Click on cell A1
Type +
Use the mouse (or press alt + tab) to switch to Workbook B
Click on cell A1
Press enter to complete the formula

Cell A2 of Workbook A would now contain the following formula =A1+[Workbook B]Sheet1!$A$1 and the result would be 10.
Excel has clearly allowed the formula to link between the two workbooks, and to retrieve data from one to the other.


In Scenario 2 (two separate instances of Excel, one having Workbook A and the other having Workbook B) this is what would happen:
Start in cell A2 of Workbook A.
Start the formula by typing =
Click on cell A1
Type +
Use the mouse (or press alt + tab) to switch to Workbook B
Click on cell A1
Press enter to complete the formula

The formula doesn't work, because Excel will not allow the formula to link between the two workbooks, and will not allow data to be retrieved from one to the other.

Cell A2 of Workbook A will have the incomplete formula =A1+ in the formula edit bar, and behaves as if you had started typing but not finished.
Workbook B will have no formula.

-----

Ben, your answer about re-sizing my Excel window to be the full width of both monitors, and then viewing the two windows side by side, is a great solution. It allows me to view both workbooks on separate screens and still do Vlookups etc between them. While the underling problem seems to be built into Windows 7/Excel 2010 (I wonder if Windows 8 and/or Excel 2013 have it fixed?) this is a great work-around and allows me to view things how I want to.

Thanks to you both for your input on this problem, it's really appreciated.

 

[bschwartz]

You're very welcome.

As an aside, I don't think that Windows 8 is going to help you. Excel 2013 I can't speak for since I haven't used it.