Solved

***500 points**  Excel-Vlookup

Posted on 2007-03-28
7
382 Views
Last Modified: 2013-12-02
We are using excel 2007 (we had the same issues with 2003).

When we move our excel document to a network share, any of the document that have vlookup no longer work or if they do work they are incredibly slow.

When all the files are on a local harddrive the response is very fast..

For example: We have a shreadsheet that the CEO users for pricing.. When he does a global "find/replace" on the spreadsheet with all the files locally, it takes about 5 seconds, (1300 replacments).. When I move that same files (all the files are in one folder) to a network drive the same find/replace takes about 5 minutes if it completes. Sometimes it crashes excel...

Has anyone else experienced this issue ?

Does any-one have an idea why this is occuring ? Does Vlookup not work across a LAN ?

This is a high priority issue as I need to get the file onto a file server but cannot upset our CEO :)

Any help or suggestions are greatly welcomed...
0
Comment
Question by:paadmin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 18808755
VLOOKUPs can be quite inefficient, particularly if they reference large ranges of data. Have you tried using INDEX/MATCH combinations instead? What are the find/replaces that are being done?
Regards,
Rory
0
 

Author Comment

by:paadmin
ID: 18808929
Hey Rorya,

I am not a excel person but our CEO lives and dies by it in everything he does.. I have never used the index/match combiniations but I will begin to look into these..

The find/replace is usually a date value but can be a price value also..

So he me.. do a find for 3.3.07 and replace it with 3.12.07

Thanks for the response,
Liam
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 18809035
If he does that a lot, it may be easier to put the required date in a cell and reference that in the formulas instead. Then you can just change the date in one cell.
If the workbooks use vlookups to return successive columns from a table, then INDEX/MATCH will be much more efficient because you can store the row number from the match in a column and then refer to that in several INDEX formulas rather than looking up the same value several times. For example, instead of:
=VLOOKUP(A1,Sheet2!A1:E1000,2,false)
=VLOOKUP(A1,Sheet2!A1:E1000,3,false)
=VLOOKUP(A1,Sheet2!A1:E1000,4,false)
=VLOOKUP(A1,Sheet2!A1:E1000,5,false)

you would use MATCH in a blank cell (say B1) to retrieve the row number:
=MATCH(A1,Sheet2!A1:A1000,0)
then refer to that cell in the INDEX formulas:
=INDEX(Sheet2!B1:B1000,B1)
=INDEX(Sheet2!C1:C1000,B1)
=INDEX(Sheet2!D1:D1000,B1)
=INDEX(Sheet2!E1:E1000,B1)

HTH
Rory
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

Author Comment

by:paadmin
ID: 18809075
I will try that with him once he's available. Would that improve the slowness across the network ?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 18809120
It should certainly help. He might also try setting calculation to manual, then doing the find/replace and then setting calculation back to automatic to see if that improves things.
0
 
LVL 5

Accepted Solution

by:
dr_shivan earned 500 total points
ID: 18855440
paadmin,

The reason for the lag is because you're transmitting data thru the Local Area Network and back. Imagine the whole world is sharing files on the server.. its like having a waiter to cater for more than 1 users.

My advise is to save a local copy to work on, and then save a backup in the server. Thus youhave 3 copies of the file which is redundant but saves computing time
0
 

Author Comment

by:paadmin
ID: 18864043
Dr Shivan,

Thanks for your response, I am trying to avoid having a copy locally due to that fact that we have 60 g.b of excel spreadsheets and this would cause a lot of confusion if we have multiple copies in several locations.

Thanks
Liam
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article was originally published on Monitis Blog, you can check it here . Today it’s fairly well known that high-performing websites and applications bring in more visitors, higher SEO, and ultimately more sales. By the same token, downtime…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question