Speed of Cross Sheet References
Hello! I'm working on a project where I use a lot of large cross sheet references, and it's sometimes very slow to update everything. I have one big summary sheet with 50 columns that are mostly vlookups to pull in data from other sheets. I want to reference the data in the summary sheet, and I was wondering if the sheet reference would be faster/work more efficiently if I split that into multiple smaller cross sheet references, or one big 50 column reference?
Answers
-
Hi @Gwen O
I generally find vlookup formulas slow my sheets as it has to process every cell in the range to get to the answer.
For example if you had a 50 column sheet with 50 rows then the calculation has to run against 2500 cells every time. this can also mean you max out your cross sheet reference cells quickly.
I tend to use Index Match whenever possible due to it minimising the volume of cells being calculated so it would look at 100 instead of the 2500 in the example above.
Hope that helps
Thanks
Paul
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!