Help with Updating Rows Via a Form Submission

Cameron Poweleit

Hello,

I am trying to use Smartsheet to track reagents, the workflow is as follows: The person prepping the reagents fills out a form with the pertinent information and the number of vials made. in this example the person made 40 vials. I would like to have this same number decrease via form submission. Example.) 1 vial is used, the person who used the vial submits a form and identifies that 1 vials was used, the number 40 changes to 39. A few weeks later somebody uses 3 vials and indicates that in their form submission, the number changed from 39 to 36. What formula would I use to have this happen/ is this sort of scenario possible?

Thank you so much,

Cameron

Paul Newcome

My suggestion would be two separate sheets. One that inserts a new row (through form submission) when vials are made and a second sheet to capture use (via form as well).

One the creation sheet you would have one column for the number created and another column that will output how many are remaining. The formula for outputting how many are left would be:

=[# Created]@row - SUMIFS({Other Sheet # Used Column}, {Other Sheet Vial Column}, @cell = [Vial Column]@row)

Paul Newcome

Are all form submissions going onto the same sheet?

Cameron Poweleit

@Paul Newcome

They could be on the same sheet, or they could come form a different sheet, whatever would make this process easier/possible

Paul Newcome

They are both possible and of the same relative complexity but different solutions. Do you currently have something operational? If not, I would be happy to share my suggestions. If you do already have something operational then we can accommodate that as well.

Cameron Poweleit

I do not have anything operational at the moment, what do you have for suggestions?

Paul Newcome

My suggestion would be two separate sheets. One that inserts a new row (through form submission) when vials are made and a second sheet to capture use (via form as well).

One the creation sheet you would have one column for the number created and another column that will output how many are remaining. The formula for outputting how many are left would be:

=[# Created]@row - SUMIFS({Other Sheet # Used Column}, {Other Sheet Vial Column}, @cell = [Vial Column]@row)

Cameron Poweleit

I have made two different sheets, the vial creation sheet and the vials used sheet. One the vial creation sheet I have two columns relating to vial use, Vials created and Vials remaining. on the Vials used sheet I have one column relating to vial use, the # of vials used (I can add more columns if need be).

On the formula you provided, I am a little confused as to what column {Other Sheet Vial Column} and [Vial Column]@row is referring to

Paul Newcome

I wrote that with the assumption that you have some type of unique identifier for each batch of vials created?

Cameron Poweleit

Thank you so much, everything is working perfectly now!

Paul Newcome

Happy to help. 👍️