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.
