Speed of Cross Sheet References

Gwen O

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?

Paul McGuinness

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