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.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!