Quantity deduction workaround

Joseph Aloysias

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

Joseph Aloysias

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!

Hollie Green

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)

Hollie Green

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)

Joseph Aloysias

@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

Hollie Green

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.

Joseph Aloysias

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

Joseph Aloysias

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,

Joseph Aloysias

@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

Hollie Green

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

Joseph Aloysias

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

Hollie Green

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)