Adding formulas to cells with data already in them...

**Kaleidoscope** · 04-17-2013, 01:56 PM

Let me start by saying I did search on google and inside the forum here for an answer to my question, but with no luck yet. So I was hoping someone could explain it to me or maybe send me a link to a post already answering this.

This is for an inventory tracking spreadsheet. What I need to do is be able to add a formula in the background that will automatically adjust the cells in that formula, while still being able to edit those cells manually. For example.. (hope I use the code button right haha)

   1            2            3
Item A    
Item B
Item C

Kit A        Kit B       Kit C

^Just a basic version of my spreadsheet

What I need to have the amounts of the kits adjust the amounts of the items, while still being able to manually enter the amount of each item. So lets say I have 10 each for items a thru c. When I make kit a, it requires item a and item b in it. So if I enter 1 for kit a, I need to to adjust items a and b to have 9 each now. Make sense so far?

With that being done, let's say I receive a shipment of item a, now I need to add 10 more to it, making the total 19. So I want to be able to change the data from 9 to 19 without effecting the formula I put in for the kits.

I hope that makes sense to everyone, tried to be as detailed with it as possible. I'm still fairly new to using excel with my new job so I hope this isn't something really easy to do that I simply just missed haha

Thanks in advance!

**FDibbins** · 04-17-2013, 02:06 PM

Hi and welcome to the forum

If I understand you correctly, you want to put a formula in a cell, and then add a value to that same cell, and keep a running total IN that same cell?

Short answer is...nope, you cannot do that with a regular formula - a cell can contain either entered data (value/text) OR a formula, it cannot contain both.

I would suggest that you create a column, and then enter data downwards in that column, and then in a separate cell, you have a formula such as =sum() to run the total for you.

Failing that, maybe some-one will write some VBA for you that could do this

**ChemistB** · 04-17-2013, 02:11 PM

I would suggest you upload a spreadsheet showing how your data is laid out and showing some results.
As FD stated, can't do what you are describing with formulas (maybe not even with helper columns) and will need VBA code to do that. It is possible with code though.

**Kaleidoscope** · 04-17-2013, 02:14 PM

Thanks for your response FDibbins! After playing around with excel for a while I thought that maybe the case but wanted to ask for sure. So is it possible to write this formula in an empty cell that would adjust the values in the cells I need?

**Kaleidoscope** · 04-17-2013, 02:25 PM

This is a screenshot of like half the spreadsheet, but it should give you an idea of what I'm working with. Thanks

Capture.jpg

**FDibbins** · 04-17-2013, 07:14 PM

Refering to post #4 - if you are asking if you can have a formula in cell, say B1, and then you keep entering data in A1, and B1 keeps a runnin total of all entries into A1 - again, the answer is no, not with a regular formula.

You would need to enter data in A1, A2, A3 etx, and have B1 sum all that data for you.

Refering to post #5, have you even looked at that picture?? It is almost impossible top see what is what in there, let alone what is doing what. It is for this very reason that we constantly advocate NOT uploading pictures, but rather a sample workbook (no sensitive info) for us to look at