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?

 

[Retrum]

If I have manual updates on my computer and I send this file to somebody and they open it will it keep the manual update feature or will it start update automatically?

 

[Aladin Akyurek]

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

Did you also consider switching to lookup formulas with the match-type set 1 for you probably have an Access output sorted in ascending order?

 

[Retrum]

I am not really sure what match-type set 1 is. Do you have an example?

 

[Aladin Akyurek]

I am not really sure what match-type set 1 is. Do you have an example?

Does this counter-question means that the data area is indeed sorted (or sortable) in ascending order on its first column? If the answer is yes, what is the current lookup formula that you use?

 

[Retrum]

It is sorted in the 1st column. My formula may be a bid ugle but I have a lot of different data.

Here is one example

=IF(ISNA(VLOOKUP($B11&"Mgmt Fees"&I$8&$U$8&$AJ$8,Data!$A$6:$O$30915,15,FALSE)),0,(VLOOKUP($B11&"Mgmt Fees"&I$8&$U$8&$AJ$8,Data!$A$6:$O$30915,15,FALSE)))

$B11&"Mgmt Fees"&I$8&$U$8&$AJ$8=1Mgmt FeesJuneActual2007
which is the code assigned for that particular value.

 

[Aladin Akyurek]

It is sorted in the 1st column. My formula may be a bid ugle but I have a lot of different data.

Here is one example

=IF(ISNA(VLOOKUP($B11&"Mgmt Fees"&I$8&$U$8&$AJ$8,Data!$A$6:$O$30915,15,FALSE)),0,(VLOOKUP($B11&"Mgmt Fees"&I$8&$U$8&$AJ$8,Data!$A$6:$O$30915,15,FALSE)))

$B11&"Mgmt Fees"&I$8&$U$8&$AJ$8=1Mgmt FeesJuneActual2007
which is the code assigned for that particular value.

Does this

=IF(LOOKUP($B11&"Mgmt Fees"&I$8&$U$8&$AJ$8,Data!$A$6:$A$30915)=$B11&"Mgmt Fees"&I$8&$U$8&$AJ$8,LOOKUP($B11&"Mgmt Fees"&I$8&$U$8&$AJ$8,Data!$A$6:$A$30915,Data!$O$6:$O$30915),0)

perform better?

 

[Retrum]

I will have to change all my formulas to test. Should this be less strain on the system? If so why is it better?