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

wisewood

Board Regular
Joined
Nov 7, 2002
Messages
193
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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Select the whole sheet, then copy it and Edit>PasteSpecial>Values
 

wisewood

Board Regular
Joined
Nov 7, 2002
Messages
193
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.
 

Forum statistics

Threads
1,181,658
Messages
5,931,270
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top