Only one sheet very slow

nisanthp

New Member
Joined
Jul 1, 2009
Messages
26
Hi all,


Im facing a problem with one sheet in an xlsx file.

The file has 3 sheets 1 this sheet, one blank sheet for future data and another one with hadly 20 rows. The file size is 361 kb only.

The sheet contains almost 700 rows and column AT. Not all the columns are occuppied only labelled till AT. but all the columns are with filter. 3 columns are having a vlookup to next sheet. the sheet is responding in snail pace, not snail is better than this. this is happening with this sheet only,

wht cud be the reason. i searched this to check some similar disucssion was here but cudnt find in this forum.

pls help.

thanx in advance
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sure.
The trick is like this:
- First, do a INDEX/MATCH combined function, but do not require an exact match (this only works on a sorted list, hence the question)
- Next, compare the INDEX/MATCH result with the item you're looking for
- If the same, do the INDEX/MATCH, this time indexing the column you actually need the data from
- If different, return NA.

Suppose we're looking for the content of cell C1 in a list of values in A2:A100 and when found, we want the corresponding cell from B2:B100.

Normally, you would use:

=VLOOKUP(C1,$A$2:$B$100,2,FALSE)

Now we use:
=IF(C1=INDEX($A$2:$A$100,MATCH(C1,$A$2:$A$100,1)),INDEX($B$2:$B$100,MATCH(C1,$A$2:$A$100,1)),NA())
 
Upvote 0
I forgot to mention: this INDEX/MATCH trick on a sorted list is about 5 times faster than a regular VLOOKUP with FALSE as last argument.
 
Upvote 0
Or, same idea,

=IF(LOOKUP(C1, $A$2:$A$100)=C1, LOOKUP(C1, $A$2:$A$100, $B$2:$B$100), NA())
 
Upvote 0
Upvote 0
The file may be currupted. Have you tried recreating it ina new workbook to see if it has the same issues?
 
Upvote 0
it is painfully slow, the one calculation page reduced to the top line even just crawls (corruption is possible)
 
Last edited:
Upvote 0
i tried to copy its long time to paste but the new file also hangs. I saved this 2003 format but the size was whooping 15mb. If u notice the problem is with the single sheet.other 2 sheets are working normal. So can the whole file be corrupted
 
Upvote 0
i've had a single page causing problems, one recommendation was to copy / cut the faulty page, paste to a new sheett, and delete the corrupted sheet.

I havent been able to do that with even just the top lines selected.

a four line vba i'm testing here is taking 3 seconds, to change four cells
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top