Solved

***500 points**  Excel-Vlookup

Posted on 2007-03-28
7
371 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
  • 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Usually shares are where we want them for our users and we tend to take them for granted. There are times, however, when those shares may disappear causing difficulty for your users. One of the first things to try is searching for files that shou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

860 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