05-06-2020 02:06 AM
05-06-2020 02:06 AM
Can anyone help me out?
I wanted to increase or decrease the quantity of a same item according to the transaction type IN or OUT from an Inventory Table. It means when I stock in (IN) the item qty will add up and when stock out (OUT) the qty will less it. But in my case as shown in the attached screenshot the quantity is showing same in all rows of respective items. I needed to be in history like first row qty 20 nos, then the next row qty will show 12 nos if it is OUT transaction.
Thank you in advance for your help.
The formula I used for "Stock Quantity" column is here
=SUMIFS([QTY IN/OUT],[TRANSACTION DATE],"<="&EOMONTH([@[TRANSACTION DATE]],0),[ITEM NO],"="&[@[ITEM NO]],[IN / OUT],"IN")-SUMIFS([QTY IN/OUT],[TRANSACTION DATE],"<="&EOMONTH([@[TRANSACTION DATE]],0),[ITEM NO],"="&[@[ITEM NO]],[IN / OUT],"OUT")
05-06-2020 02:49 AMSolution
@vppismail I would like to recommend you to insert an extra column that sets the quantity to plus or minus based on IN/OUT. Then you can use what you could call and auto-expanding SUMIF to calculate the closing inventory after each transaction for a particular item. Easiest to demonstrate in a working example. Please see attached.
05-06-2020 07:35 AM
Thank you for your quick reply..
It is working in normal cell ranges but in a table it is not working, it showing the same qty in all respective item's row (as highlighted in the excel).
I have attached the excel file for your reference
05-06-2020 08:47 AM
@vppismail In this case, I believe you have to sacrifice the structured table references and manually enter the first and last range in the following in N21:
05-06-2020 09:21 AM
@Riny_van_Eekelen It worked , thank you so much, appreciated.
05-07-2020 01:31 AM - edited 05-07-2020 02:10 AM
Sorry to bother you, you helped me a lot and I am very glad to be with you.
This formula is consistent, not updating accordingly when add new rows in this table, so if it is possible you may peruse the attached excel last two rows and also please try to add new rows with data then you can understand the issue.
05-07-2020 02:54 AM
I notices an inconsistency on row 41. The last element in the formula is M42. It should be M41. Can't tell what you did to cause this. I copied the formula from the row above to the rows below it and all works fine. Then, I added a row to the table and the formula is automatically included in that new row.
05-07-2020 03:35 AM
@Riny_van_Eekelen May be you haven't noticed by adding row by pressing TAB. When I solve by clicking "Restore to calculated column formula" then it is corrected by itself. When I add new row by pressing TAB then the just above row will be inconsistent by itself changing the value to M42 instead of M41
05-07-2020 04:19 AM - edited 05-07-2020 04:20 AM
@vppismail I see. Didn't notice that before. I guess it's because we are not exactly following "best practices" here by violation against structured table references. Experimented a little and found that if the formula looks like below, the inconsistencies does not occur.
Don't really know how to explain why the @-sign makes this happen. Perhaps others out here who can explain.
05-07-2020 05:13 AM
@Riny_van_Eekelen Thank you for your great support.
And the formula is not working.
Anyone can help me out.
05-11-2020 05:24 AM
@Riny_van_Eekelen I got it from another excel forum website and it solved by using this formula for structure table
=SUMIF(Table14[[#Headers],[ITEM NO]]:INDEX(G:G,ROW([@[ITEM NO]])),[@[ITEM NO]],Table14[[#Headers],[QTY +/-]]:INDEX(M:M,ROW([@[QTY +/-]])))
05-11-2020 08:10 AM
@vppismail Thanks for sharing. Took the formula to test it in a schedule of my own. I learned something today. But, on my system (Excel for Mac and Excel for PC on a virtual machine), the formula I provided earlier returns the same outcome. Thanks again!
05-11-2020 09:40 AM
@Riny_van_Eekelen You are welcome, I also learnt a lot from you, thanks again.