Creating running stock

jouko

New Member
Joined
Oct 5, 2014
Messages
4
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 numberAmount boughtAmount soldSelling price
A001662€
A001311,5€
A002545€
A003444€
A0031624€

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

DateProduct numberSelling PriceAmount soldTotal purcase price
xx.xx.2014A0011,5€23€
xx.xx.2014A0012€12€
xx.xx.2014A0025€14€
xx.xx.2014A0034€312€
xx.xx.2014A0012€24€

<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 numberAmount boughtAmount soldAmount leftSelling price
A2= A001B2= 6C2= 6D2= 0E2= 2€
A3= A001B3= 3C3= 1D3= 2E3= 1,5€
A0025415€
A0034404€
A003162144€

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

Some videos you may like

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
Joined
Feb 7, 2012
Messages
12,446
Office Version
  1. 2013
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,329
Messages
5,528,033
Members
409,799
Latest member
mlewan_ca

This Week's Hot Topics

Top