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

 

[meriambambu]

thank you so much.

 

[meriambambu]

Hello again, i tried to experience with external data as a reference formula but i am stuck at finding the right function.
I've triend to use count and sum and it is working fine.
This two function works fine,
=SUM([sales.xls]Sheet1!C2:C20)
=COUNT('C:\Users\test\Downloads\[sales.xls]Sheet1'!B1:b21)
But when i tried to combine using the formula like this
=$I$4-SUMPRODUCT(($M$4:$M$100=$H$4)*($N$4:$N$100)) (working formula)
=$I$4-SUMPRODUCT(($M$4:$M$100=$H$4)*('C:\Users\test\Downloads\[sales.xls]Sheet1'!N4:N100)) (error).
Do you have any idea how to solve this?

 

[oldbrewer]

in your spreadsheet have a column - let us say col AA - in AA1 put = C:\Users\test\Downloads\[sales.xls]Sheet1'!N1

then use that column in the sumproduct

 

[meriambambu]

So there must be a value for the paramether in the same worksheet?

 

[oldbrewer]

I don't know ! try it and if it does what you want try to reference another sheet...

 

[oldbrewer]

red
blue
red
blue
red
				10
				SUMPRODUCT(($A$1:$A$5="blue")*(Sheet2!$B$1:$B$5))
				this refderences a different sheet in same workbook

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