Quantity deduction workaround
I want to deduct the total "quantity requested" in the "final stock of inventory" after selected the "collected" check box
and also wanted to show the available stock before confirming the order, Please help me with the workaround/ formula to achieve this
Currently I use this formula, =[Initial Stock]@row - IF(Collected@row, 0, SUMIF([Item Name]$1:[Item Name]@row, [Item Name]@row, [Total Quantity Requested]$1:[Total Quantity Requested]@row)) but its not working as expected
Answers
-
I want to minus the total qunatity requested from final stock of inventory if the collected checkbox is checked, if the checkbox is not selected then it should show the remaining quantity but here final stock of inventory and available stock-before calculation is not right, i have been trying hard to solve this issue. please help me with the right formula.
using this formula in "final stock of inventory" : =[Initial Stock]@row - IF(Collected@row, SUMIF([Item Name]$1:[Item Name]@row, [Item Name]@row, [Total Quantity Requested]$1:[Total Quantity Requested]@row))
this formula in - available stock-Before : =IF(Collected@row, [Final Stock of Inventory]@row + [Total Quantity Requested]@row, [Final Stock of Inventory]@row)
@Paul Newcome @Genevieve P. @SoS | Dan Palenchar PLEASE HELP!
-
For the Final Stock Inventory Column, if I am understanding correctly you are wanting to subtract the total requested inventory from the row above that is checked from the initial stock and if the collected box is not check you want it to show the quantity that was available on the last row where the checked box occured.
If I am understanding correctly the below formula should work - @Domnic Victor edited formula if you saw before forgot to include the item name criteria.
=[Initial Stock]@row - SUMIFS([Total Quantity Requested]$1:[Total Quantity Requested]@row, Collected$1:Collected@row, 1, [Item Name]$1:[Item Name]@row, [Item Name]@row)
-
For the Available Stock - Before, if I am understanding correctly and you want it to add the Total Quantity requested and the Final Stock inventory if the box is checked and if the box is not checked then put the Final Stock Inventory then the below formula should work.
=IF(Collected@row = 1, [Total Quantity Requested]@row + [Final Stock Inventory]@row, [Final Stock Inventory]@row)
-
@Hollie Green Thank you so much. First formula works very well.
But second formula which will show available stock before is not working properly, Its adding the qty if the check box is unselected
=IF(Collected@row = "1", [Final Stock of Inventory]@row, [Total Quantity Requested]@row + [Final Stock of Inventory]@row)
Could you please check and advise
-
You have it backwards If you look at my formula above I have the addition part of the formula right after the collected@row=1 and you have it at the end. The order matters in an If formula.
-
yeah, that's working. Thanks a lot
Another one thing is I want to bring the final stock of inventory to inventory master sheet to show the remaining qty
=IFERROR(INDEX(COLLECT({eStore: Request Sheet - Final Stock of Inventory}, {E-Store: Request Sheet - Item Name}, @cell = [Item Name]@row), COUNTIFS({E-Store: Request Sheet - Item Name}, @cell = [Item Name]@row)), [Total Stock Qty]@row)
I used the above formula to get it but I get #INVALID COLUMN VALUE error if the items are not available in the request sheet
-
Hi, I would like to subtract quantity from final stock of inventory if the collected checkbox is checked, =IF(Collected@row = "1", [Final Stock of Inventory]@row, [Total Quantity Requested]@row + [Final Stock of Inventory]@row) I used this formula to calculate but the problem here is if I select the check box for the last order(ORD0285) the previous quantity is not showing correct, can you please help with the right formula,
-
@Hollie Green please help! the previous order is not showing the right quantity.
The idea is to subtract the quantity if the collected checkbox is checked, I want to show what was available before and after the order, please help me with the workaround
-
What should it be showing? It looks accurate to me as before ORD0284 there were 5 available and ORD0284 has not been collected so the amount available after that order should still be 5
-
after checking the collected checkbox the final stock of inventory is "2" but ord262,ord263,ord264,ord283 its still showing "5" correct me if i'm wrong
-
It depends on what you are wanting the final stock column to say.
From my understanding you wanted it be what the final stock was after that particular order with them going in order. If you want the final stock number to drop based on all collected orders regardless if the Order was placed before or after that order then it would be a different formula and all of the final stock number would be the same number which in your example would be 2 including on rows ORDO260 and ORD0261
The formula for that would be =[Initial Stock]@row - SUMIFS([Total Quantity Requested]:[Total Quantity Requested], [Item Name]:[Item Name], =[Item Name]@row, Collected:Collected, =1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!