0

I have created a Google Form that is used for people in the office to request items (swag given to donors). After an item or items are requested, the request must be fulfilled by a team member.

I would like to use the form in conjunction with the linked Google Sheet to create an inventory sheet that will automatically update once a request has been completed.

Things I currently have:

  • A working Google Form that populates Sheet 1 of a Google Sheets workbook with responses detailing which items are requested and how many. To this, I have added a column where team members can indicate if they have fulfilled a particular request.
  • A current inventory of items in Sheet 2 of the workbook, that currently must be manually updated as requests are fulfilled and inventory decreases.

Goals:

  • When a team member indicates a request has been filled, the inventory on Sheet 2 automatically deducts the appropriate amount of each item requested from current inventory.

Is this possible with Google Forms/Sheets? The several methods I attempted before (using the SUMIFS function, creating separate inventory updaters for each item) did not work because when Google Forms populates the first sheet, it adds a row with the new information in it for each new response to the form. This caused any formulas I had attached to Sheet 1 to be shifted downwards to empty cells instead of calling the form response information and not work properly.

Here are some examples of things I attempted:
First, I tried creating columns at the end of the form responses sheet that would copy the number of items requested (columns, T, U, and V). These columns would only fill in if the request had been fulfilled. Here is the formula I attempted to use to have the inventory sheet match the name of the item requested and then update that item's inventory:

=C2 - (SUMIF('Request Form'!$F$2:$F$103,B2, 'Request Form'!$T$4) + (SUMIF('Request Form'!$H$2:$H$103,B2, 'Request Form'!$U$4)) + (SUMIF('Request Form'!$J$2:$J$103,B2, 'Request Form'!$V$4)))

Columns F, H, and J correspond to the item names requested. Columns B and C here refer to the item's name on the inventory spreadsheet and the current inventory count of that item.

In this version of the Google Form, team members could choose an item name from a drop-down list and then enter a requested quantity. The maximum amount of different items that could be requested at once was three. However, now the form involves choosing any number of items at a time. Here are some other attempts:

=C2-SUMIFS('Request Form'!$T$2:$T$102,'Request Form'!$F$2:$F$102,{B2})

=C2 - IFS('Request Form'!$F$2:$F$103 = B2,'Request Form'!T4,AND('Request Form'!$H$2:$H$103 = B2,'Request Form'!U4),0,AND('Request Form'!$J$2:$J$103 = B2,'Request Form'!V4),0)

=C2 - (IF('Request Form'!$F$2:$F$103=B2,'Request Form'!$T$4,IF('Request Form'!$H$2:$H$103,B2, 'Request Form'!$U$4)))

I also tried creating separate sheets for each of the 37 items and updating inventories separately like this: =IF('Form Responses'!A2 = "Y", 'Form Responses'!K2, 0) where column A indicated if the request had been fulfilled. Then, all these separate inventories would be referred to by the master inventory sheet.

In all these situations I ran into the same issue where my formulas did not line up once new responses came in from the form.

If anyone can offer advice about how to make this work, I would be really excited to learn from you.

  • 1
    Could you also please add a sample form and the corresponding sheet? It would help understanding the structure of your form, where you placed your formulas etc. – marikamitsos Aug 20 at 2:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.