changes in one column affecting the next

[hotdogs1999]

Hello,

Don't really know how to describe this, but pretty much I'm wanting to be able to type any number (positive or negative) in column "D" and then i want it to automatically change the corresponding cell in Column "E" by that amount.

E.g. cell E7 has value of 19 currently. i then enter -4 into cell D7 and press enter, and the number in E7 automatically drops to 15.

(and i want to be able to keep on doing this multiple times - not just a one off situation).

THanks in advance.

 

[kpasa]

Hello,

Don't really know how to describe this, but pretty much I'm wanting to be able to type any number (positive or negative) in column "D" and then i want it to automatically change the corresponding cell in Column "E" by that amount.

E.g. cell E7 has value of 19 currently. i then enter -4 into cell D7 and press enter, and the number in E7 automatically drops to 15.

(and i want to be able to keep on doing this multiple times - not just a one off situation).

THanks in advance.

You'll want to look into the Change event for VBA. VBA can monitor when a worksheet has changed and fire off a macro. So if you listen for a change, set the macro to check if the change occurred in the column you wanted. If true, then write another macro/function to do your calculation. Here's a link to more documentation. This should get you started: https://support.microsoft.com/en-us...un-a-macro-when-certain-cells-change-in-excel

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in column D and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    Target.Offset(0, 1) = Target.Offset(0, 1) + Target
End Sub

 

[hotdogs1999]

great. thanks. that works.

is it possible to have the value in column D disappear once it has been entered? so that it doesn't stay visible?

thanks

 

[kpasa]

is it possible to have the value in column D disappear once it has been entered? so that it doesn't stay visible?

Very simple addition:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    Target.Offset(0, 1) = Target.Offset(0, 1) + Target
    Application.EnableEvents = False
        Target = ""
    Application.EnableEvents = True
End Sub

 

[hotdogs1999]

thanks for you help. works great.