# Creating running stock

#### jouko

##### New Member
The situation:

Sheet 1

I have Excel 2013. I have made a table for my products that I sell where I always create a new row when I:
1. Change the selling price for the goods or
2. Order something from my suppliers (the purchase price is always different).

These rows contain following info for example:
Product number, Amount bought, Amount sold, Price for sold item.

Example:

 Product number Amount bought Amount sold Selling price A001 6 6 2€ A001 3 1 1,5€ A002 5 4 5€ A003 4 4 4€ A003 16 2 4€

<tbody>
</tbody>

Sheet 2

Recently I created a sheet in the same workbook which has the customer order information. Every order creates a new row. They're formatted as follows:

Date, Product number, selling price, amount sold, total purchase price

Example 2:

 Date Product number Selling Price Amount sold Total purcase price xx.xx.2014 A001 1,5€ 2 3€ xx.xx.2014 A001 2€ 1 2€ xx.xx.2014 A002 5€ 1 4€ xx.xx.2014 A003 4€ 3 12€ xx.xx.2014 A001 2€ 2 4€

<tbody>
</tbody>

These examples don't contain all the columns I use.

The broblem

I used to input all the data in the "Sheet 1" manually, but now I have an Excel feed for the customer sales. I'd like to create a running stock from the feed in "Sheet 2". I would like to do it as in this next example into the "Sheet 1".

 Product number Amount bought Amount sold Amount left Selling price A2= A001 B2= 6 C2= 6 D2= 0 E2= 2€ A3= A001 B3= 3 C3= 1 D3= 2 E3= 1,5€ A002 5 4 1 5€ A003 4 4 0 4€ A003 16 2 14 4€

<tbody>
</tbody>

So the exception to the original "Sheet 1" would be that Excel would calculate the "Amount sold" automatically from the "Sheet 2" feed. Another function would be that when D2=0, Excel would start adding the "Amount bought" numbers to the next "A001" product row, in this case into the cell C3.

I don't know which formulas to use in my example to get where I want.

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### JLGWhiz

##### Well-known Member
You can try this in your revised Sheet 1 table to see if you get the results you want for column D.
Put this formula in D2 and then drag it down the column as far as you have data in columns B and C.
Code:
``=If(B2>C2,B2-C2,0)``

Replies
3
Views
40
Replies
5
Views
97
Replies
0
Views
70
Replies
16
Views
165
Replies
14
Views
209