Automatically Reduce Stock from selling list

[meriambambu]

I would like to create a file that can reduce stock automatically from my starting inventory based on the name of item that was sold.

here is a download link for my sample file

 

[oldbrewer]

inventory
apples	375
oranges	510
pears	230
				before sale of pears
				inventory
				apples	375
				oranges	510
				pears	240
sales
pears	10	done
				this macro checks all sales
				and if not marked done
				updates inventory
				and marks that sale "done"
				For j = 20 To 100
				If Cells(j, 1) = "" Then GoTo 100
				If Cells(j, 3) <> "" Then GoTo 50
				For k = 3 To 5
				If Cells(k, 1) <> Cells(j, 1) Then GoTo 20
				Cells(k, 2) = Cells(k, 2) - Cells(j, 2): Cells(j, 3) = "done": GoTo 50
				20 Next k
				50 Next j
				100 End Sub

<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[meriambambu]

can u make it more simple like using function? i am not familiar with the use of macro.

 

[oldbrewer]

a macro just does a few calculations and changes the contents of specified cells. This one uses 2 loops to move through your data. Say A1 is the number of apples = 100. You sell 15 apples - how do you propose to change A1 from 100 to 85 ? It CAN be done but it is not more simple.

 

[oldbrewer]

pears	3				initial stock	100	apples			100 is in cell H1
lemons	7
apples	2
oranges	4				current stock	78	apples
pears	3
lemons	7
apples	2
oranges	4
pears	7
lemons	2
apples	4					formula giving 78
oranges	2
pears	4					=$H$1-SUMPRODUCT(($A$1:$A$100=$I$1)*($B$1:$B$100))
lemons	3
apples	7
oranges	2
pears	4					I set the limit to A100 so you can add sales in the future
lemons	3
apples	7					easy to add new purchases of apples to the formula
oranges	5

<colgroup><col span="14"></colgroup><tbody>
</tbody>

 

[meriambambu]

thanks mate, this is exactly what i am looking for.

 

[meriambambu]

Hello again oldbrewer,

I am having difficulty with the formula.
This is the sample file that i manage now, i have to copy the function 1 by 1 with some modifications.
FYI, stock out always 1 by 1 based on the item name.

 

[oldbrewer]

the formula has to have YOUR cell references in it, of course (sorry I never download)

 

[meriambambu]

The result become like this

=$I$4-SUMPRODUCT(($M$4:$M$100=$H$4)*($N$4:$N$100))

=$I$5-SUMPRODUCT(($M$4:$M$100=$H$5)*($N$4:$N$100))

=$I$6-SUMPRODUCT(($M$4:$M$100=$H$6)*($N$4:$N$100))

=$I$7-SUMPRODUCT(($M$4:$M$100=$H$7)*($N$4:$N$100))=$I$4-SUMPRODUCT(($M$4:$M$100=$H$4)*($N$4:$N$100))=$I$5-SUMPRODUCT(($M$4:$M$100=$H$5)*($N$4:$N$100)) =$I$6-SUMPRODUCT(($M$4:$M$100=$H$6)*($N$4:$N$100)) =$I$7-SUMPRODUCT(($M$4:$M$100=$H$7)*($N$4:$N$100)) This formula cell references were input by me manually. Can i make it automatically increase by 1 cell reference ?

<colgroup><col></colgroup><tbody>
</tbody>

 

[oldbrewer]

if you want to increase down from A1 if a1=b1+c1

you do not want a $ before the 1

so you start with $I4 not $i$4