Plus or Minus based on text

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

Find more posts tagged with

Formulas

IT & Operations

Project Management

Comments

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

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

Richard Rymill SBP

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?