Long calculation time under a small size file

Dear All,

I recently wrote an inventory excel file and regardless of the file size the calculation time seem ridiculously long (20-30 seconds). I have already recreated different visions of formulas in numbers of trial runs.

• Could it be the number of formulas that increase the calculation time?
• Could it be the number of conditions within the formulas that affect the time?
• or Could it be the multiple locations of the look up value requested from different sheets?

Attached file as an example of my file.

Thanks in advance.
Mike

Dear leelnich,

Thanks a lot. It does shorten the time. I am so used to the full-column references which seems like a bad habit.

Regards,
Mike

Stay tuned, I'm working on a few formulae, too. Example: in Dispatched!D2 and down:
=IF(ISBLANK(B2),"",(INDEX('Job#'!$E$2:$E$5000 &" under Job#: "&'Job#'!$F$2:$F$5000,MATCH(A2,'Job#'!$B$2:$B$5000,0))))

About Dispatched!D2 and down, I planned to show the last entry from 'Job#' Sheet (Which might contain duplicated data) and that's why I used Lookup(2... instead of Index/Match. Mostly because I am not sure if I could have function:Index/Match return the last entry.

Ok, Try this. I finished the Range conversions and changed that one formula. Now it's really quick!

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee