FORMULAS bringing Excel to a halt - need advice

[PWM]

Hi Folks -

I am working with a very large database of timesheet entries - 200,000+ rows. The file itself is about 28MB. Until recently the file has been manageable, but I suspect the addition of 2 formulas is causing Excel to hang even when I try to do simple selections etc. I'm no Excel guru, so I'm certain my formulas are not ideal or efficient. I am hoping someone can take pity on a relative newbie and give me some advice about the following formulas. I'd prefer not to post the doc itself (or a part of it) if not absolutely necessary I imagine that just looking at my formulas some of you may be able to recognize obvious issues, and perhaps be able to suggest better approaches using other types of formulas.

Thanks!

=IF(ISBLANK([@[Pipeline Step]]),INDEX('Roles by Name'!A:B,MATCH([@Person],'Roles by Name'!$A$1:$A$290,0),2),(IF([@[Pipeline Step]]="STB - Standby",INDEX('Roles by Name'!A:B,MATCH([@Person],'Roles by Name'!$A$1:$A$290,0),2),INDEX('Roles by Step'!A:B,MATCH([@[Pipeline Step]],'Roles by Step'!$A$1:$A$57,0),2))))


=IF([@Task]="Standby","WFA",IF([@Type]="Depot Management","WFA",IF([@Type]="Vacation","PTO",IF([@Type]="Paid Holiday","PTO",IF([@Type]="Sick Personal Day","PTO",IF([@Type]="Unpaid Time Off","UTO",IF([@Type]="Sales","SALES",IF([@Type]="Pipeline","PIPE",IF([@Type]="Project",IF([@ProdType]="Internal","WFA","PROJ"),IF([@Type]="Engineering", IF([@Dept]= "Artists", "DOWN","ENG"),IF([@Type]="Admin","WFA",IF([@Type]="Training",IF(SUM(IF(ISNUMBER(SEARCH('Key Words'!$A$2:$A$14,[@Notes])),1,0))>0,"TRAIN","WFA")
))))))))))))

[Melvinrobb]

Anytime you use ranges such as "A:B" you are referencing EVERY SINGLE ROW in those columns (over 1 million rows, over two columns, meaning 2 million cells!). Changing the range to $A$1:$A$250000 would eliminate a majority of the references from your formulas. That is one easy improvement.
Do some research on Dynamic Ranges, as they would also help in making the range only be as large as it has to be, rather than the entire column.

[PWM]

Anytime you use ranges such as "A:B" you are referencing EVERY SINGLE ROW in those columns (over 1 million rows, over two columns, meaning 2 million cells!). Changing the range to $A$1:$A$250000 would eliminate a majority of the references from your formulas. That is one easy improvement.
Do some research on Dynamic Ranges, as they would also help in making the range only be as large as it has to be, rather than the entire column.

Thanks so much for this advice - very helpful I'll be sure to boost your rep!

I'll leave the post open a while longer in case anyone else wants to share some pointers as well.