We have a spreadsheet for keeping track of invoices, what they're for, how much is owed etc etc.
Each invoice is saved in a directory, and then the master sheet looking at specific cells within those invoices to get their values - eg. =SUM('G:\Marketing\Invoices\[MISC2654.xls]Sheet1'!$E$34:$R$34)
Now, the problem is, we now have almost 3000 of these invoices, and the master sheet takes a VERY long time to open because of all the linked data held in other .xls files.
is there another, more efficient way, to collect the data from these external files? Perhaps a way to load the information into the master sheet only once, and then set the value of that cell to save the need to load it in again? That way only updates are loaded, rather than ALL the information every time.
please say this makes sense lol
Each invoice is saved in a directory, and then the master sheet looking at specific cells within those invoices to get their values - eg. =SUM('G:\Marketing\Invoices\[MISC2654.xls]Sheet1'!$E$34:$R$34)
Now, the problem is, we now have almost 3000 of these invoices, and the master sheet takes a VERY long time to open because of all the linked data held in other .xls files.
is there another, more efficient way, to collect the data from these external files? Perhaps a way to load the information into the master sheet only once, and then set the value of that cell to save the need to load it in again? That way only updates are loaded, rather than ALL the information every time.
please say this makes sense lol
