Formula to Count Qty from an Order Sheet

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

Tags:

Best Answer

  • Amit Wadhwani
    Amit Wadhwani ✭✭✭✭✭✭
    Answer ✓

    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/

    Best Regards

    Amit Wadhwani, Smartsheet Community Champion

    Smartsheet CoE, Ignatiuz Software, Exton, PA

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


    Did this answer help you? Show some love by marking this answer as "Insightful 💡" or "Awesome ❤️" and "Vote Up ⬆️"

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!