Update Row cross-sheet Examples?

Dan West_TX

I am new to Smartsheets so I'm looking for a bit of help...

What I am trying to do:

I want to use a form to subtract/update the inventory on my Smartsheet. I know a form cannot do that directly. I have seen a lot of Q&A regarding updating rows from sheet to sheet and the answer is always to use VLOOKUP or INDEX/Match. But with no examples that I have found so far. So here is what I have:

I have a parts inventory sheet with a unique identifier for each row and a column "Qty on Hand"

I have a blank "inventory update sheet" with a "Qty Removed" column

And I have a form to be used to populate the inventory update sheet for the parts being removed from the inventory. The Unique ID for the part is a required field.

Can someone please show me an example of how this is done? Or point me to a good example?

Thank you!

Genevieve P.

Hey @Dan West_TX

I'd be happy to help!

You'll want one column that identifies the original quantity on hand, which I am assuming is your "Parts Inventory Sheet" with the column "Qty on Hand".

Then you can have a second column that is "Remaining Qty" where you put your cross-sheet formula. In this instance, I would use a SUMIFS statement to SUM together all the Qty that's being recorded in your Inventory Update sheet, like so:

=SUMIFS({QTY Column Update Sheet}, {Unique Identifier Update Sheet}, [Unique Identifier]@row)

Then you can subtract this SUM from the original "Qty on Hand", like so:

=[Qty on Hand]@row - SUMIFS({QTY Column Update Sheet}, {Unique Identifier Update Sheet}, [Unique Identifier]@row)

Let me know if this is what you were looking for!

Cheers,

Genevieve