If cell has a value already...

[Smurfit-Stone]

Hello Board, I need to know if its possible to take a column of numbers and create a formula where if there is currently a value in the cell, take that number and also add from other columns....i.e. cell M2 has a value of 20 (no formula), but I want to add the value of 13 in cell D2 and I2 has a value of 4 . Is there a way to add a formula to column M that will add the 20+13+4 and give me a total of 37, then I could drag the formula down column M to all the other cells. They all have different values or numbers in them. Thanks in advance.

Teryl

 

[lenze]

Unfortunately, if you input a formula in M2, it will overwrite the value already there. You might also have a circular referenc. This can be done, however, using VBA.

Sub AddMeUp()
Dim cl As Range
For Each cl In Range("$M$2:$M" & Range("$M$65536").End(xlUp).Row)
cl = cl + cl.Offset(0, -9) + cl.Offset(0, -4)
Next cl
End Sub

lenze

 

[Smurfit-Stone]

I pasted this code onto my spreadsht, but now what? the calculation doesn't happen. I'm I doing something wrong. I need the value in column M to increase by the values in columns D and I. If a cell in M already has a value, I need to add that plus whatever is entered into D and I...like a running total. Thanks for you help.

 

[Smurfit-Stone]

I pasted this code onto my spreadsht, but now what? the calculation doesn't happen. I'm I doing something wrong. I need the value in column M to increase by the values in columns D and I. If a cell in M already has a value, I need to add that plus whatever is entered into D and I...like a running total. Thanks for you help.

 

[Smurfit-Stone]

I pasted this code onto my spreadsht, but now what? the calculation doesn't happen. I'm I doing something wrong. I need the value in column M to increase by the values in columns D and I. If a cell in M already has a value, I need to add that plus whatever is entered into D and I...like a running total. Thanks for you help.

 

[lenze]

The code does work. Open the VBE(Alt+F11) Choose Insert>Module. Paste the code in the panel. Exit the VBE(Alt+Q). Choose Tools>Macros. Select "AddMeUp" and click Run.

I may, however be reading your intent wrong. Do you want to have column M update whenever a value is entered in column D or I? If so, we can use a change event. If that's what you want, post back and I will write the code to do that.

HTH
lenze

 

[Smurfit-Stone]

Actually, your right, the code does work...but I said column M but its actually column N. Can I just change the code wherever it refers to M to N and get it to work?

 

[Richard Schollar]

Hi

You also need to amend the offset that Lenze applied (as you are 1 column further to the right):

Sub AddMeUp() 
Dim cl As Range 
For Each cl In Range("$N$2:$N" & Range("$N$65536").End(xlUp).Row) 
cl = cl + cl.Offset(0, -10) + cl.Offset(0, -5) 
Next cl 
End Sub

 

[Smurfit-Stone]

Thanks...it works beautifully. You guys are awesome. I truly feel Blessed everytime I come to this site. Thanks again!

Teryl