[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

***500 points**  Excel-Vlookup

Posted on 2007-03-28
7
Medium Priority
?
401 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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 the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Suggested Courses

656 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