Lookup in a different *instance* of Excel (not just different *window*)

[serpentine]

I am trying to use VLOOKUP to look up values in a second workbook opened in a second instance of Excel, that is, Excel is opened twice*. This is not two windows or workbooks within a single instance of Excel. In the older, better versions of Excel this *would* be called two different windows. But in Excel 2010 I don't even think Microsoft has a word for it.

My question is:

When the Function Arguments window pops up, and you go to Table Array, and then try to select a range of cells in the other instance, the Table Array field does not get completed. Excel thinks you are simply selecting cells in the second instance like any ordinary cell selection. So the Table Array field remains blank.

Is there way to get the Table Array field in one instance to automatically fill when you click and drag in the other instance?

Thanks!


* Having multiple instance open is preferred by many power users, especially those with multiple monitors. Trying to use one instance of Excel with multiple workbooks on multiple monitors is a massive headache.

 

[AD_Taylor]

Hi welcome to the forum!

I know what you mean by Excel causing a headache when working in multiple files with dual screens. I've had the same problem myself :P

However as far as I know what you're asking for is impossible. If you want to set up the table array that way, both files will need to be in the same instance of Excel. Once you've got the VLOOKUP set up for the first time though you should be able to close the second workbook and open it in a separate instance and still retain the functionality. Excel will just create a link to the files path and therefore once this link is created you can operate them side by side without an issue.

HTH!

 

[serpentine]

Hi welcome to the forum!

Thanks!

I know what you mean by Excel causing a headache when working in multiple files with dual screens. I've had the same problem myself

OK, so I'm not alone! I've been suffering with one instance spread across two screens for a year and then it finally dawned on me to google it, and I found a registry hack to allow multiple instances. So at least that's working. Is 'instances' even the right word for this?

However as far as I know what you're asking for is impossible.

Oh crap. Yeah, the paths seem fine when created in one instance then opened in another and so forth. Maybe some superbrain out there has created some sort of hack ;^) It's kind of an obscure thing though; I'm not even sure how I'd google it.

 

[RoryA]

It's just not possible. The single best thing in 2013 for me so far is multiple monitors in one instance is so easy!

 

[piginabush]

[Sorry, just realised am using Excel 2007 but thought would leave here as I have already typed it!!]

Workbook A open on 1st Monitor, Workbook B open and showing on 2nd Monitor.

Data you want to lookup in Workbook A, and results returned in Workbook B.....

In desired cell in Workbook B, type as follows;

=VLOOKUP(A1,'C:\My Files\My Excel\[Workbook A.xls]Component'!A1:C3,2)

Where....

A1 = Data you are looking for, in Workbook B
'C:\My Files\My Excel\[Workbook A.xls = Location of Workbook A
Component = Tab Name
A1:C3 = Location of table in Workbook A
2 = returned column number

When you press Enter, you should get a File Search box, you need to select the location of Workbook A and click OK - this tells the two versions of Excel to speak to each other. You can check by going to Data>Edit Links where the location of your linked Workbook is.

In order to update the values (for Workbook B, where the VLOOKUP is) go to Data>Edit Links and open the link as Read Only, then close it straight away, this updates any values in Workbook A.

I use dual-screen excel spreadsheets all the time and this works!