Only one sheet very slow

[nisanthp]

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

 

[jkpieterse]

I can think of a number of things:

- many objects on the sheet (possibly hidden or of zero width)
- A lot of Conditional formatting.

Excel 2007 has a bug where copying a range with a CF on top of another range with also a CF will add the copied CF on top of the existing one, rather than replacing the CF. If your sheet undergoes a lot of copying and pasting and has CF applied, this may cause lots and lots of CF rules in your sheet.

 

[nisanthp]

thnx jkpieterse for the quick reply.

there's no conditional formatting. i cleared all rules 2-3 times to make sure of any hidden CF.

I didnt understand " many objects on the sheet (possibly hidden or of zero width)"

 

[mole999]

so you have 700 lines * 3 vlookups (2100 vlookups that have to be refreshed, hence some of the speed issues)

can you post each of the lookups, and maybe someone can suggest a less busy way of ensuring you have a quicker system

 

[nisanthp]

dear mole..

i changed vlookup into sumif to check if tht is the cause, but that also didnt help. sumif formula is this one.. similarly one more sumif is there

1. =+SUMIF('Mark Up Category'!$A$35:$B$39,BOQ!AE585,'Mark Up Category'!$B$35:$B$39)


the vlookup formula is preceded by an if(). is this the reason??
=IF(D3>0,VLOOKUP(D3,'Mark Up Category'!$A$4:$B$27,2,FALSE),)

all vlookups are similar formula only change in row ref:

 

[mole999]

I understand the principles, but I'm not good at writing optimally

I believe vlookup is volatile

and any change on the sheet causes a recalculation, so if your vlookup changes one value that is then used elsewhere, you get a cascade of updates until all done

 

[ZVI]

VLOOKUP is not volatile. But each time the filter changes, formulas in a filter range are calculated.
Therefore, the faster the formulas the better filter’s response.

Page setup (i.e. Print Area or Titles) can slow down cells navigation.
Temporarily clear all sheet's print settings to test it influence

 

[jkpieterse]

Is the list you are doing the lookup in sorted on the key column?

 

[nisanthp]

no it's not sorted... will tht help??

 

[jkpieterse]

No, if it were a sorted list you could benefit from doing a special INDEX/MATCH combination. Could you sort it?