If cell has a value already...

Smurfit-Stone

Active Member
Joined
Dec 1, 2004
Messages
485
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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.
Code:
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

Active Member
Joined
Dec 1, 2004
Messages
485
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

Active Member
Joined
Dec 1, 2004
Messages
485
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

Active Member
Joined
Dec 1, 2004
Messages
485
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Active Member
Joined
Dec 1, 2004
Messages
485
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

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

Code:
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

Active Member
Joined
Dec 1, 2004
Messages
485
Thanks...it works beautifully. You guys are awesome. I truly feel Blessed everytime I come to this site. Thanks again!

Teryl
 

Forum statistics

Threads
1,181,416
Messages
5,929,790
Members
436,693
Latest member
BroTr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top