Reset cell content from a SUM() to the result of the SUM

[wisewood]

I am looking for a way to save hours of time each week currently taken up by waiting for a massive spreadsheet to load.

The workbook in question has several links to external data, contained within almost 3000 .xls documents… which as you can imagine take a long time to load, and quite often cause Excel to stop responding if we have more than one or two applications running.

Is there any way to set the value of each cell which is being linked to the value that is displayed in the cell, rather than the value of the formula that is contained within the cell.

=SUM('G:\Marketing\Invoices\[MISC2654.xls]Sheet1'!$E$34:$R$34)

The above for example, might have a value of 1000, which would be displayed to the user – but this data actually comes from the external source and is loaded each time with excel. Once that value has been loaded once, I want to set the value of the cell to 1000, instead of =SUM('G:\Marketing\Invoices\[MISC2654.xls]Sheet1'!$E$34:$R$34).

Does this make sense? Please help. It’s wasting lots of time waiting for this spreadsheet to load.

 

[Lewiy]

Select the whole sheet, then copy it and Edit>PasteSpecial>Values

 

[wisewood]

I suppose really all i am after is a way to copy the value of cells within a certain range, and paste special : values

Now i think about it, i could just do this manually if there is no smarter way to do this.

 

[wisewood]

Select the whole sheet, then copy it and Edit>PasteSpecial>Values

thanks.