Log In
Register
Home
July 11, 2017
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
July 11, 2017
Offline
Hi Sunny,
Have removed the password,
Thanks
Paul
VIP
Trusted Members
June 25, 2016
Offline
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
1 Guest(s)
