Hello all,

I hope that someone can help with me with my problem which I have been struggling with for a couple of days:

I received a complex spreadsheet and my task is to improve its performance and increase the speed of calculations. Currently, it takes up to 25 minutes to calculate the spreadsheet! I have read that INDIRECT formulas should be avoided when creating a complex spreadsheet. Unfortunately, I found out that its using a lot of them which is why I think it causes the slow calculations :-( So, I would like to get rid of them if possible.

My question which formulas are suited best to replace INDIRECT functions and if they actually can be avoided (please look at my example formula below). One of many formulas which include INDIRECT functions…

=IF(OR($A108="PL";$A108="non-PL");IF($C108=1;SUMPRODUCT((CATEGORY=$B108)*(SUPPLIER=$F108)*(YEAR='other meta data'!$Q$2);TOTAL)+SUMPRODUCT((CATEGORY_C=$B108)*(SUPPLIER_C=$F108)*(YEAR_C='other meta data'!$Q$2);SPEND_C);IF($C108=2;SUMPRODUCT((Category_PC=$B108)*(SUPPLIER_PC=$D108)*(YEAR_PC='other meta data'!$Q$2);TOTAL_PC);""));IF(ODER($A108="Total PL";$A108="Total non-PL";$A108="Total Category Spend");SUMPRODUCT(((INDIRECT(ADRESS(ROW()-80;2)):INDIRECT(ADRESS(ROW()-1;2)))=$B108)*(((INDIRECT(ADRESS(ROW()-80;1)):INDIRECT(ADRESS(ROW()-1;1)))="PL")*(($A108="Total PL")+($A108="Total Category Spend"))+((INDIRECT(ADRESS(ROW()-80;1)):INDIRECT(ADRESS(ROW()-1;1)))="non-PL")*(($A108="Total non-PL")+($A108="Total Category Spend")));(INDIRECT(ADRESS(ROW()-80;COLUMN())):INDIRECT(ADRESS(ROW()-1;COLUMN()))));""))

I would greatly appreciate it if someone could help me. I can provide more information if necessary.

Thank you very much,
pepperjoe