Forum

Notifications
Clear all

Indirect & Offset formulas are seriously slowing my spreadsheet calculations

6 Posts
3 Users
0 Reactions
103 Views
Philip Treacy
(@philipt)
Posts: 1632
Member Admin
Topic starter
 

Hi Sunny,

I have attached an example, but have had to slim the file down as original is 7,000kb and has 15 tabs with a total of 143,000 INDIRECT and OFFSET formulas

Is there anyway to convert to a VLOOKUP formula keeping the 2 MATCH formulas?

Regards

Paul

 
Posted : 19/10/2018 7:45 am
(@sunnykow)
Posts: 1417
Noble Member
 

Hi Paul

143K INDIRECT and OFFSET, no wonder your calculation slowed down to a crawl.

143K VLOOKUP will also cause a problem.

I would suggest you set your file's calculation to MANUAL to prevent the calculation after every edit while we try to sort it out.

I don't think I will be able to test the speed even if I suggest another method as I don't have so much data to play with.

BTW your file is password protected and I am unable to open it.

Sunny

 
Posted : 19/10/2018 11:24 am
(@benjamip)
Posts: 89
Estimable Member
 

Hi Sunny,

Have removed the password,

Thanks

Paul

 
Posted : 21/10/2018 4:44 am
(@sunnykow)
Posts: 1417
Noble Member
 

Hi Paul

In cell G3 of the Occupancy sheet enter

=VLOOKUP($A3,'REV OCC'!$E:$AW,MATCH(G$2&$A$1,'REV OCC'!$E$7:$AW$7,0),FALSE)

No idea if it will increase the calculation speed.

Do let us know the result when compared to the original using INDIRECT.

Hope this helps.

Sunny

 
Posted : 21/10/2018 2:20 pm
(@benjamip)
Posts: 89
Estimable Member
 

Hi Sunny,

I have updated all 143,000 formulas and calc speed is back to normal

Thanks for your help

Regards

Paul

 
Posted : 22/10/2018 11:14 am
(@sunnykow)
Posts: 1417
Noble Member
 

Hi Paul

That's great!!

Glad to know it is working well for you.

Sunny

 
Posted : 22/10/2018 11:22 am
Share: