MrExcel Publishing
Your One Stop for Excel Tips & Solutions

anyone had problems w/vlookup & network files?


Posted by Daniel P. on November 16, 2001 10:15 AM

I've setup a vlookup formula in a network file (named "master") that references another network file (named "values"). When "master" is opened and I select to update, excel freezes every time. It does not freeze however if I have "values" open.

I think I've ruled out it being a problem within the "master" spreadsheet. I saved "master" to my hard-drive and had the same problem. If both files are on my hard-drive and formulas changed, the "master" file WILL update whether the "value" file is open or not.

Any ideas on what's going on? Can I lookup information from a file on a network without opening it first?

Daniel P.


Posted by Jack on November 16, 2001 12:06 PM


Via Vlookup formula you cant lookup data must be visable (OPEN BOOKS), i understand that VBA can look in closed SS but thast beyond me, lets hope a programmer catches this post.

Posted by Dave bour on November 19, 2001 8:34 AM

Daniel,
I do this extensively. Across a network, it is VERY SLOW. Ie, I've one set of data that looksup some values. I found it easier to store in the network file, the number of lines of data, copy that data to the local sheet and perform my lookup there. It runs about 50% faster. I've got a client who has this setup over a 10Mb network. I'm running a 100 and it still takes upwards of 2-3 minutes to do the lookups. On the 10Mb, the system is "hung" for 30+ minutes. My newest thing I'm trying is building subset sheets to reduce the range I need to transfer initially, which has my current runtime/lookups down from 15 minutes now to about 8 seconds though I've troubles gettting the filters to work consistently to pick the right subset of data.
Dave