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:
<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:
<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".
<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.
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.
