Adding numbers in the same cell

**JimLau** · 09-27-2012, 08:18 PM

how can I take a number from one cell and add it to a number from another cell and keep the answer in the same cell

THANKS RICHARD!

**Jakobshavn** · 09-27-2012, 08:33 PM

You are describing an accumulator. Say you enter 4 in A1 and A2 becomes 4. If you now enter 7 in A1, A2 becomes 11. If you then enter 9 in A1, A2 becomes 20.

You can set this up with a macro.
Is this what you want?

**Richard Buttrey** · 09-27-2012, 08:39 PM

Hi,

You would need to use a macro. Something like

Range("A2") = Range("A2")+Range("A1")

**JimLau** · 09-27-2012, 08:54 PM

Yes this is exactly what I want with a twist. Not only do I need to update the number but I have to find the Part in goes to on the same row. there are 32 parts in that column. So I have to find it first the update it.

**Richard Buttrey** · 09-27-2012, 09:08 PM

Hi,

I find it difficult to visualise what you describe. Can you give some specific examples please?

**JimLau** · 09-27-2012, 09:15 PM

I have an Inventory file and of to the side of the table I have two cells one for the part# and one for the amount the user will adjust their inventory by. They can't go to the Qt. cell because it is update by another function. So I need to find the Part # in one column and then update the Qt. in another column on the same row. Am I sounding nuts!

**JimLau** · 09-27-2012, 09:18 PM

Sorry Richard:
One other thing, if this is done in a macro. Can the macro run when they hit the enter key after they put the number in the cell.

**Richard Buttrey** · 09-27-2012, 09:56 PM

Hi,

Yes, you'd use a Sheet_Change macro to update the relevant line that contains the Quantity.
I suggest that you add a third helper cell which uses an =MATCH() function to identify which row of your table contains the Part # you enter (either directly or via a drop down containing all part numbers) and then use that helper cell in the macro.

Untested since I don't have direct access to Excel at the moment - but assuming the part numbers are all in column A, with quantities in Column B, the part number in say D1 and the Quantity to adjust in E1, with a helper cell in F1 which contains the function

Formula:

Please Login or Register  to view this content.

Name the helper cell say "UpdateRow"

Now the macro would be

Please Login or Register  to view this content.

**JimLau** · 09-27-2012, 10:56 PM

Again Sorry Richard:
This is a little confusing but I getting it some what, let me give you more exact details. I put the =match in cell W14 and it returns the proper row #. the Part # that I am entering to look for is in W6 and the Qt. # I am entering to add to the Qt. Already in place is in U6. The list of part #'s are in C4:C35 and the number I am trying to add to is in F4:F35. I didn't quite understand what you meant by naming the help cell "UpdateRow"

**Richard Buttrey** · 09-27-2012, 11:10 PM

Hi,

All I meant was that you should apply a name to the W14 cell and then use the name in the macro rather than the absolute W14 cell reference. As a general rule you should use named cells/ranges in functions and macro code for all sorts of good reasons, not least that formulae are easier to read. You could instead use

Please Login or Register  to view this content.

but you can probably see that if you were to change or move the W14 helper cell then you'd also need to edit the macro, whereas if you always name cells then you never need to worry about editing the macro. For the same reason it would also be a good idea to name the W6 quantity cell and use that name rather than the W6 reference.