Automating a change in value when a condition is met at another sheet


I have two smartsheets with part numbers. When a request for a part number is made, I want to decrement the number on hand that is kept in another smartsheet. How can I link the two smartsheets and automate the mathematical subtraction?


  • Jake Gustafson
    Jake Gustafson ✭✭✭✭✭✭

    I'm assuming you have a standard Quantity on Hand column (QoH). From there when the request is made in another sheet, then could you do a lookup (to subtract the requested quantities). If part number equals the same, then total the requested quantities from all rows in the request sheet. Now that's assuming you're retaining all those requests, and then would you also have a restock sheet needing another formula to add stock quantities back in?

    I'm thinking a SUMIFS to total the requested amount (making it negative) and subtracting it from your base stock QoH value.

  • Serdar

    Hey Jake,

    You are totally on the right track in your assumptions and thinking. The question I have is:

    • How do I subtract quantity pulled that is handled in one sheet from QoH in another sheet without creating additional columns?