Which Formulas are Slowing Down my Sheet?

Hello, I've been working on multiple Excel sheets with their own Pivot Tables and Dashboards. One sheet in particular is quite slow...

It takes 45 seconds to load - bottom corner says Calculating (4 Processors).

Unfortunately it contains a lot of sensitive information so I will detail the formulas used and give you an idea of how many times they are done:

x672 cells:

=IFERROR(VLOOKUP("2015-01",'K:\Profit Reports\[Monthly Profit Report.xlsx]IndivSalesBySalesperson'!$A:$B,2,FALSE),0)

x1855 rows containing the following cells:
x12 standard data entry
x6 data validation dropdown

=IFERROR(VLOOKUP(A1860,'K:\Profit Reports\[Monthly Profit Report.xlsx]TotalsByQuote'!$A:$B,2,FALSE),"No Data")

=IFERROR(VLOOKUP(A1860,'K:\Profit Reports\[Monthly Profit Report.xlsx]TotalsByQuote'!$A:$D,4,FALSE),"No Data")

=IFERROR(VLOOKUP(A1860,'K:\Profit Reports\[Monthly Profit Report.xlsx]TotalsByQuote'!$A:$E,5,FALSE),"No Data")

=[@[Reason Lost]]

=IFERROR(IF([@[Date In]]="","1999-01",TEXT([@[Date In]],"yyyy-mm")),"1/1/1999")

=IFERROR(TEXT([@[First Ship Date]],"yyyy-mm"),"")

=[@Status]

=[@[Acquisition Likelihood]]>0.49

=[@[Acquisition Likelihood]]>0.59

=[@[Acquisition Likelihood]]>0.69

=[@[Acquisition Likelihood]]>0.79

=[@[Acquisition Likelihood]]>0.89

=[@[Acquisition Likelihood]]=1

=IFERROR(VLOOKUP(A1858,'K:\Profit Reports\[Monthly Profit Report.xlsx]TotalsByQuote'!$H:$I,2,FALSE),DATEVALUE("1/1/1999"))

=TEXT([@[Contract Date]],"yyyy-mm")

There you go... hoping some of you might be able to notice something off the top of your head that I am doing wrong and causing too much calculation time.
I do have everything set to automatic updates because it is a shared document and the other users will not learn how/remember to do manual calculations.
Thank you in advance for the help!

Hi HWScott,

Potentially the ranges in your vlookup formulas as they reference entire columns rather than defined ranges.

Regards,

Snook

The_Snook, thank you. Now that you mention it, I do remember that rule of thumb. What do you recommend? Creating dynamic named ranges? Or is there a way to say "look in this row as far as the table it is in extends"?

TMS, I think you are on to something. Yes, I just tried opening this sheet with the source sheet open and it loaded instantly. Is there anything that can be done to increase the speed with the source workbook closed?

My thanks to you both for the help so far!

Originally Posted by shirleyxls

If you create a named range based on column(s) in the table, that range will always extend to the last row of the table. Useful for data validation lists too

Aha, very good to know.

What about for pivot tables? I realized that in order to make a dynamic named range for pivot tables, I end up using a helper cell that references an entire column anyway... COUNTA(A:A)-3... minus 3 for the pivot report filter, column title and grand total. That gives me the dynamic figure that I put in my OFFSET named range formula to know how far to go down. So if I'm using a full column reference anyway, I don't think I'm speeding anything up.

Thanks!