2

I have a sheet in Excel with a list of numbers. Sometimes I want to move X units from one cell to another.

Let's say I have a classroom of kids, each one has a certain number of marbles. There are 2 columns in the sheet, first column is names, second column is marbles. John gives 375 marbles to Bill. I want to update the sheet.

Currently I pull out a calculator, do the subtraction and addition, and update the cells manually.

This is cumbersome and error-prone. Is there a better way?

(I know I can write "=8300+375" in the cell but eventually each cell will have a stupidly long formula..)

Improve this question
3
  • 2
    I only know how to do it with a formula. I am not sure what you mean by 'without a formula' as everything in Excel is done with formulas. Maybe you should post a screenshot with a description of just what you are trying to do because I, as well as I am sure others, are confused at this point.
    – Rich Manson
    Commented Mar 18, 2019 at 13:41
  • 1
    If you truly want to replace the value in D4 then you have to do this manually, or create a macro. There's no other way (even with a crazy formula, as formulas can't replace text). But perhaps you can explain a little more what you're trying to do? Can you not put the difference in another cell, or do you have to replace D3?
    – BruceWayne
    Commented Mar 18, 2019 at 14:59
  • Let's say I have a classroom of kids, each one has a certain number of marbles. There are 2 columns in the sheet, first column is names, second column is marbles. John gives 375 marbles to Bill. I want to update the sheet.
    – CaptainCodeman
    Commented Mar 18, 2019 at 22:23

2 Answers 2

Reset to default
2

You can use paste special to easily perform the calculations.

  • Enter -375 in one cell and 375 next to it.
  • select your new numbers and press CTRL+C
  • Select the cells you want to change.
  • in the ribbon / home / paste / "paste special"
  • check "add" then "ok"

enter image description here

Improve this answer
1

Máté Juhász post inspired me to suggest you VBA Macro, Since it's faster and time saver too. And I do believe that You can handle it.

enter image description here

How it works:

  • I'm assuming that you have data in Range A2:B4.

  • Put Values you want to Subtract in C2:C4.

  • From Developer TAB, click Design Mode
    then Insert & From ActiveX Controls
    click Command Button Icon and draw it on
    the Sheet.

  • Click the Command Button, you reach to VB editor Windows.

  • Copy & Paste this code between

    Private Sub CommandButton1_Click()

    End Sub.

    Range("C2:C4").Select
 Selection.Copy
  Range("B2").Select
  Selection.PasteSpecial 
  Paste:=xlPasteAll, 
  Operation:=xlSubtract, SkipBlanks:= _
  False, Transpose:=False
  Range("C2:C4").Select
  Selection.ClearContents
  Application.CutCopyMode = False

  • From VB editor Windows click File menu then hit Close & Return to Microsoft Excel.

  • Finally Click on Command Button.

You find New Values in Column B,(check Screen shot).

N.B.

If you want to Subtract Old vlaue from New Value in a Cell only, then you may use this VBA code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column = 2 And Target.Row = 1 Then

  NewVal = Range("B1").Value
    Application.EnableEvents = False
     Application.Undo

   OldVal = Range("B1").Value
    Range("B1").Value = NewVal
     Range("B1").Value = NewVal - OldVal
      Application.EnableEvents = True

End If
End Sub

  • Press ALT+F11 to open VB editor and Copy & Paste the above shown code as Standard Module.

  • As soon you overwrite Value in B1, code will
    Subtract it from Old Value and re-write B1
    with New Value.

Adjust Data Range as your need.

Improve this answer

You must log in to answer this question.

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