vlookups slowing down my computer

[Retrum]

I have created a spreadsheet with mulitple tabs each tab has vlookups back to a main data tab. I have added quite a few tabs with a lot of vlookups. All of the sudden Excel is really slowing down and in the bottom right corner it does calculating cells with a %. It does this everytime I do something on my spreadsheet. Takes about 30 seconds to do.

I have tried breaking my spreadsheet up into 2 different files and get the same results.

Any suggestions?

 

[Joe4]

If you have that many relationships that you have a lot of sheets and a ton of VLOOKUPS, this project might be better suited to be done as a relational database, like in Access or SQL.

In the meantime, you could always set your calculation mode to manual. Then when you make changes, it won't recalc the formulas every time (until you do so by hitting the F9 key). This can be done under Tools | Options | Calculation | Manual.

 

[timorrill]

If it is not necessary for all the fields to be recalculated after every change, you can set calculation mode to 'Manual'. To do this, choose "Options..." from the "Tools" menu. On the "Calculation" tab choose "Manual". You may also want to choose "Recalculate before save". To recalculate the workbook at any time, simply press F9.

Hope this helps.

 

[timorrill]

Another thought... if the data on the main tab does not change frequently, you can copy and paste the values only. To do this, select the range where the VLOOKUPS are stored, and choose Edit | Copy. Then, while the range is still selected, choose Edit | Paste Special... | Values.

 

[Retrum]

I will make the change to manual calc.

What I have is a ms query on the 1st tab that pulls data form Access then vlookups on each tab to do reporting. So each value needs to be looked up. Thought about doing it in Acess but the reports are much easier to create and send out in Excel.

 

[Retrum]

is there a way to stop it from calculating while it is doing it?

 

[Joe4]

Since you already have the data in Access, I would strongly recommend looking at converting to Access altogether. There are no need for VLOOKUPS in Access, the relational aspect of the database takes care of that inherently if you set up the table/query relationships correctly. Also, Access's reporting capabilities are much more powerful than Excel's.

Of course, it would take a little time and effort to do the conversion, especially if you are not real familiar with Access...

 

[Retrum]

I am pretty familiar with access. One ? I have is how do you send out reports that you create in access via email?

 

[Joe4]

Try searching the board for sending reports via email in Access. I got a number of hits, including this one:

http://www.mrexcel.com/board2/viewtopic.php?t=191583&highlight=send+reports+email

 

[Retrum]

Thanks I will check it out. The manual calc is working much better thanks.