Forum Discussion
Solved
Excel Workbooks with external workbook links loading extremely slowly
Using Excel via Office 365, I have noticed a dramatic increase in the amount of time required to open any member of a group of files, each of which have links to other workbooks. These are files...
I fixed this issue for myself by changing from each cell having an individual formula, to the sheet using an array instead.
You do this by selecting all of the cells you want to take from the other document, going to the other document, selecting all of the data on that side, then hitting Ctrl + Shift + Enter.The formula will end up looking like this
{=path/[data_document.xlsx]Sheet1'!A1:Z500}
FAIR WARNING: If you do this over empty cells, they will become 0 instead of empty, so if you have code that expects a string, you will get a data type mismatch error.
Thank you for the workaround. Unfortunately, that won't work for my application because I can't have zeros and I use the data in the links to create custom text.
It probably still won't totally work for you but you *can* use the array reference as part of an IF statement, in the form
IF(ExternalWorkbookReference="","",ExternalWorkbookReference).
This will leave all of your imported empty cells as empty cells but will still not increase load time.
IF(ExternalWorkbookReference="","",ExternalWorkbookReference).
This will leave all of your imported empty cells as empty cells but will still not increase load time.