Solved

***500 points**  Excel-Vlookup

Posted on 2007-03-28
7
369 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:paadmin
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
INTRODUCTION The purpose of this document is to demonstrate the Installation and configuration, of the HP EVA 4400 SAN Storage. The name , IP and the WWN ID’s used here are not the real ones. ABOUT THE STORAGE For most of you reading this, you …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

728 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now