Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Plus or Minus based on text

Scott Lund
Scott Lund ✭
edited 12/09/19 in Archived 2017 Posts

I am currently setting up an inventory management sheet that has a webform. I want to be able to have data updated based on one column.

What I have is a column that is Shipped / Received.

When completing the form, if the user chooses Shipped, the QTY column displays -10 and then takes this away from total stock on hand. If they choose Received, it displays as 10 and adds this to the stock on hand. 

What would be the formula to have for the QTY column to display this based on the Shipped / Received column. 

Thanks in advance

 

 

Tags:
· Share on FacebookShare on Twitter

Comments

  • Richard Rymill SBP
    Richard Rymill SBP ✭✭✭✭✭✭

    Scott

    Hopefully you are NOT wanting the Smartform to automatically update the quantity column while the form is being completed because you can't. 

    However the really simple way to see column totals is to use Hierarchy, indent all rows below the header row and input the formula =SUM(CHILDREN()) that will do exactly what you seek within the Sheet itself. All new rows will be included in the SUM. 

    Just in case.... If you did want to apply conditional logic calculations in a live form you would need to use Appsheet to manipulate your Smartsheet data for you and that can bring in further benefits.. 

     

    Hope that helps 

    RichardR

     

     

    · Share on FacebookShare on Twitter
  • Scott Lund
    Scott Lund ✭

    Hi Richard, 

    I have the QTY doing what I need it to, just more making the numbers a negative if the user submits a form with Shipping out of the two options of Shipping / Receiving. That way the user doesn't need to input that symbol, as I need to make it a simple as possible for the end user

     

     

    · Share on FacebookShare on Twitter
  • Richard Rymill SBP
    Richard Rymill SBP ✭✭✭✭✭✭
    edited 06/19/17

    Scott, good to know and always looking for the most user friendly entry, I agree with you. My colleagues CraigW or JamesR can probably write you a complex formula to do this but i would just have 3 columns, IN,   Out and then NETT of the two which is an easy formula which will replicate as new rows are added below. 

    If you cannot easily do that yourself, then post your exact column names and i'm sure we can get you an exact formula to copy in?

    RichardR 

    Smarterbizpro

     

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories