How to subtract quantities from one sheet via forms to another sheet with total quantities

phildyche

My issue is this:

I have a master sheet with material types (A1,A2,A3 etc..) and total material quantities (#'s). The other sheet I am using is updated via forms. The form is used for distributing materials and requires a material type, and material quantity before submission.

Once submitted I would like the quantity to be subtracted from my "total material quantities" from my master sheet. The issue is that there are multiple material types with the names, "A1","A2","A3" etc....

So lets say I have two form submissions that have updated my forms sheet. One submission is for 5 "A1" materials. Another submission is for 3 "A2" materials. What function can I use to locate "A1" or "A2" material quantities and have them subtracted from my master sheet.

All help is much appreciated😀

SolutionSal

Hello, sounds like you're in need of a SUMIF formula.

Your master sheet could have a [Quantity Used] column with the formula written below. You'll need to create cross sheet references to your 'material request' form sheet. One for the material type column and another for the quantity.

=SUMIF( {material type}, "A1", {quantity} )

This could then be used to calculate a [Current Quantity] column with a formula like so

= [Start Quantity]@row - [Quantity Used]@row

Hope this points you in the right direction!

SolutionSal

Hello, sounds like you're in need of a SUMIF formula.

Your master sheet could have a [Quantity Used] column with the formula written below. You'll need to create cross sheet references to your 'material request' form sheet. One for the material type column and another for the quantity.

=SUMIF( {material type}, "A1", {quantity} )

This could then be used to calculate a [Current Quantity] column with a formula like so

= [Start Quantity]@row - [Quantity Used]@row

Hope this points you in the right direction!

phildyche

Thank you so much! this solved my issue