Formula to Count Qty from an Order Sheet

Cheryl Collins

Hi

I have an order sheet that holds the quantity of an item being ordered. The sheet also shows whether there is enough of the item in stock or not.

If the item is in stock I need to minus the qty from the corresponding item on the stock sheet. If the Item is not in stock, once the job is completed, it needs to add the qty made to the relevant item on the stock sheet.

I think I probably need a few helper columns and some COLLECT formulas somewhere but, I'm happy to share the sheets with anyone that could help.

TIA

Cheryl

Amit Wadhwani

Hi @Cheryl Collins

Yes you would need some helper columns to calculate the Stocks.

You can add new columns in your sheet with the help of INDEX/MATCH formula get the In Stock value in the same sheet now, add one more column called “Available Quantity” and do the calculations in these column.

Let me know if you need more details on how this will work.

Best Regards

Amit Wadhwani, Smartsheet CoE, Ignatiuz Software, Exton, PA

https://www.linkedin.com/in/amitinddr/

Amit Wadhwani

Hi @Cheryl Collins

Yes you would need some helper columns to calculate the Stocks.

You can add new columns in your sheet with the help of INDEX/MATCH formula get the In Stock value in the same sheet now, add one more column called “Available Quantity” and do the calculations in these column.

Let me know if you need more details on how this will work.

Best Regards

Amit Wadhwani, Smartsheet CoE, Ignatiuz Software, Exton, PA

https://www.linkedin.com/in/amitinddr/

Cheryl Collins

Hi @Amit Wadhwani

Thanks, let me have a go and I will come back to you if I get stuck! :-)

Cheryl