• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup

B

bpnmurthy

New Member
In a excel file, there are many tab's and on of the sheet has the vlookup formula and the formula covering the whole tab's in the sheet, in each cell I need to refresh the formula. In a particular cell pressing F2 and entering, but to referesh its taking 3 to 4 minutes. there are many cells to be refersh but its taking longer time.


Kindly let me know why is this taking somuch of time to referesh, any ways are there to refresh it fast.


Thanks in advance.
 
Bpnmurthy


Firstly, Welcome to the Chandoo.org Forums


Have you got Calculation set to Automatic or Manual?


Goto the Formulas, Calculation Options Tab and set it to Automatic
 
Hi Hui,

Thank you somuch for your answer.

I have got the calculation as automatic only,

But again i will try, If it is same, I will get back to you.


Thanks,

Murthy.
 
If you are in automatic mode, you might check out Debra's article here:

http://blog.contextures.com/archives/2012/02/16/excel-formulas-not-calculating/


She walks through pretty much every scenario that would cause things to no calculate correctly.
 
1/If you have many VLOOKUPs referring to other sheets, the slow down will be considerable.

2/If you are using exact match for the VLOOKUPs it will be slower.

Add those two together and you can easily have a 3-4 minute calculation. From the sound of it, you have such a large number of formula dependencies that the smart recalculation no longer functions and a full recalculation occurs each time a formula recalculates. You may also have to press CTRL+ALT+F9 or CTRL+ALT+SHIFT+F9 to correctly recalculate the entire workbook.

I have to deal with such a monstrosity at work, but am not in a position to change it. The work-around band-aid approach I use is to turn calculation to manual and do a CTRL+ALT+F9 recalc only when I am ready for a recalculation.
 
@Kyle McGhee

Hi!

I'm facing a similar issue at work too. When you find a little of free time, would you be as kind as giving a look at this topic? Maybe something can be done... Thanks in advance.

http://chandoo.org/forums/topic/udf-lose-their-value-until-ctr-alt-f9

Regards!
 
You must log in or register to reply here.
Back
Top