0

I have a spreadsheet with dollar amounts that i occasionally need to add to. is there a way to set it up that i can type a number into a cell, and it will automatically add it to the current value already in the cell?
ex:
+--------+---------+----------+-------+-------+
|----------- Jan ---| Feb ---| March | April |
+--------+---------+----------+-------+-------+
| Costs | 230----| 756----| 1234--| 1543 |
+--------+---------+----------+-------+-------+
| Income | 450 - | 1867 - | 2104 | 2354 |
+--------+---------+----------+-------+-------+
sorry that it came out a bit unclear. If I would want to add 250 to the Income in January, is there a formula i can use to add it by typing in that square?

ps. if not, is there an efficient alternative, like a separate cell (call it D26) that i can put a value in that will add it to the income cell and won't be removed from the income cell if i delete the contents of D26?

2
  • You will have to be a bit clearer than that to get a coherent response. Can you post what you've tried so far, sample data and expected output? You may want to read this Aug 27, 2018 at 18:37
  • Some diagrams or screen shots of your data would help us understand your question. Please edit your question to include some example (mock) data (before and after). See Format Text as a Table for a web utility that will help you to create a nice data table you can paste into your question. Upload any image(s) to Imgur and you will get link(s) you can share. Edit your question to include the link(s) and someone with sufficient reputation will inline the image(s) for you.
    – DavidPostill
    Aug 27, 2018 at 19:49

3 Answers 3

2

You can't do exactly what you're requesting but there are a couple of options.

Option 1 - use a formula

You already have value 450 stored in your January income. You can convert it to a formula =450+250. You don't need to retype the original value - press F2 to edit it.

Option 2 - paste special

  1. Type your value 250 into an arbitrary cell (you suggested D26).
  2. Copy the cell
  3. Select the target cell (January income)
  4. Paste special, Paste:Values, Operation:Add
  5. Optionally clear the value from arbitrary cell

Personally I prefer option 1 because the formula can represent the history of the cell. It started with 450 then had 250 added.

2
  • You're mistaken. You can do EXACTLY what is being asked. Aug 28, 2018 at 10:07
  • 1
    if it can be done, can you please post an answer?
    – Antheloth
    Aug 28, 2018 at 11:03
1

No, because when you enter a value into a cell in any spreadsheet, be it Excel, Libre Office, Apache Open Office, Google Sheets, or others, it replaces the previous value.

4
  • I am not very good with excel but can't something be made in such a way that column A has the grand total and has a formula in it where each cell in column A can be refreshed to something like C1=C1+e1(where e1 might be on a different sheet which we can call raw data) and then when you refresh the main sheet you can get the added values?
    – CAO
    Aug 27, 2018 at 18:54
  • @CAO - yes, except that what you describe is not what I understood Antheloth to describe, hence my asking for sample data, etc. Aug 27, 2018 at 19:33
  • @CAO putting 'C1 = C1 + E1' inside a cell cause most Excel users to get a circular reference error with "0" in C1 no matter what value is in E1. Walking through the formula: let C1 = 1 and E1 = 2, so C1 = 1 + 2, obviously 3; because C1 is adding itself to number and C1 is now 3 we have C1 = 3 + 2, and then C1 = 5 + 2 ... and so on. Iterative calculations can be enabled up to X iterations but it is off by default. Aug 28, 2018 at 11:10
  • Ahh, didn't know that. Thanks. I was thinking we could make it some run time thing which runs only when we want it to and then resets the values of the cells in raw data sheet to zero
    – CAO
    Aug 28, 2018 at 12:37
1

There are ways of doing what you want but they are NOT easy. They are quite difficult and require that you write a macro and have the macro running all the time to track the pre-existing contents of the cell and then add the new input to the existing values. The method you ask about also has the disadvantage that you will lose the history of what numbers you have already added into the cell. It also has the disadvantage that if you accidentally mistype a number and it replaces the pre-existing number with the pre-existing number plus the new number, the sheet will not show a record of what numbers you have already typed in, and it will be very difficult for you to fix things. I suggest a simple spreadsheet such as in this. This first image shows the sheet as it would appear when you are using it. SuperUser.com won't allow me to insert the images I prepared because I don't have enough points yet. But do this.

Cell A10, enter Income

Cell B9. enter Jan

Cell C9, enter Feb

Cell D9, enter Mar

Cell B10 enter 450

Cell B11 enter 250

Cell B13 enter "=B10+B11+B12" (without the quotes.)

I'm not sure if this helps or not, but it is the way I would do what you are asking about. I would avoid, at all costs, anything that overwrites the contents of a cell.

1
  • I gave +1 for being the only answer to acknowledge a fact: it is possible to satisfy the OP's requirements. But you're a bit off on a few things. The program doesn't need to be running all the time with the worksheet change event. History isn't lost and restoration/error recovery is retained if cell edits are logged to a file procedures are written to reference it. And finally, I would agree that thisi adds complexity but the difficulty change is purely relative. Aug 28, 2018 at 10:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .