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
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
Hi Sunny,
Have removed the password,
Thanks
Paul
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
Hi Sunny,
I have updated all 143,000 formulas and calc speed is back to normal
Thanks for your help
Regards
Paul
Hi Paul
That's great!!
Glad to know it is working well for you.
Sunny
