Excel - SUM Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Is it possible to keep the values in a cell, where that value is calculated from a formula, after the data for that formula has been deleted? I have data that will be deleted after a certain date, but I want to keep the sum from that data even after it is deleted.
Solved by X. L. in 12 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 10/10/2018 - 11:55
Welcome to ExcelChat!
Excelchat Expert 10/10/2018 - 11:55
I see that your question is about keeping the formula values. I will help you with the problem and explain the solution afterwards.
Excelchat Expert 10/10/2018 - 11:55
Before we get started, this is a reminder that our policy is one problem per session with additional Q&A on that problem as time allows.
Excelchat Expert 10/10/2018 - 11:55
Let me ask you a couple of quick questions to make sure I fully understand your problem.
User 10/10/2018 - 11:55
okay :)
Excelchat Expert 10/10/2018 - 11:56
So you have some formula calculations, which are based on the data which you plan to remove, right? But you want to keep the values of those calculations, right?
User 10/10/2018 - 11:56
Correct
Excelchat Expert 10/10/2018 - 11:56
To do this, simple the ranges where you have the formulas
Excelchat Expert 10/10/2018 - 11:57
Then right click -> paste special -> paste values
Excelchat Expert 10/10/2018 - 11:57
First copy the ranges where you have the formulas
User 10/10/2018 - 11:57
I want to write a formula that tells excel to save the values that it created in that cell. I don't want to have to go in and copy and paste every time.
Excelchat Expert 10/10/2018 - 11:58
Well, sorry, but that is not possible with built in features. Some custom VBA solution would be required for that. Currently we don't support them.
User 10/10/2018 - 11:59
well, is there a formula that can automatically copy and paste
Excelchat Expert 10/10/2018 - 11:59
No, formulas don't do the operations with the document, only custom script can do
Excelchat Expert 10/10/2018 - 11:59
So if you remove the data based on which you had the result
Excelchat Expert 11/10/2018 - 12:00
It will disappear from the document as well, because Excel can't remember what was stored in the cells
User 11/10/2018 - 12:01
okay is there somewhere I can find a list of all of excel's formulas with a description of how each one is used?
Excelchat Expert 11/10/2018 - 12:02
Yes, but trust me, none of the formula can do it (I had many projects where only formulas were allowed, and many my clients wanted to have this possibility, but it doesn't exist). Non of the function in Excel or Google Sheets can change the value of other cells. Some functions from Google Sheets can display the data in many cells, but they can't write the data.
Excelchat Expert 11/10/2018 - 12:02
Here is the link for Google Sheets
Excelchat Expert 11/10/2018 - 12:03
https://support.google.com/docs/table/25273
User 11/10/2018 - 12:04
Can i have the link for Excel's function list please? or is it the same one
Excelchat Expert 11/10/2018 - 12:04
Here is Excel:
Excelchat Expert 11/10/2018 - 12:04
https://support.office.com/en-us/article/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188
User 11/10/2018 - 12:04
Thank you!
Excelchat Expert 11/10/2018 - 12:05
This is the main difference of formulas and script - formula only show the information, it doesn't write it.
User 11/10/2018 - 12:05
So what exactly is script?
Excelchat Expert 11/10/2018 - 12:06
It is custom action performed to the document. It is made using the special programming language.
Excelchat Expert 11/10/2018 - 12:06
For Excel it is VBA, for Google Sheets it is Google Apps Scripts
Excelchat Expert 11/10/2018 - 12:06
Currently we don't support them, but there is a chance that we will start soon
User 11/10/2018 - 12:07
ok, thanks for the help!
Excelchat Expert 11/10/2018 - 12:07
You are welcome, it was a pleasure to assist you! If it happens that you have any Excel or Google Sheets problems in future, please don't hesitate to reach us again.
Excelchat Expert 11/10/2018 - 12:07
You may now end the session, I would highly appreciate if you rate our session at the end and leave some feedback. Have a nice day!

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc