How to Create a form that updates part of an existing record?

Walid Sulayman

I have a request tracking sheet where the requestor fills a form to register a new request (record/ row). Later in the process, I need the person actioning this request to open a form that allows selection of any request (record/ row) to be updated and eventually marked complete.

How can I set up the second form?

Paul Newcome

You would need to either use Update Requests or a separate form with formulas (INDEX/MATCH) to pull the data into the original row.

Walid Sulayman

Thanks Paul! Could you please elaborate on the index/match idea?

Ideally, after a request is taken through the request intake form, the team should be able to open another form, have a drop down field to select which request they wish to update and then update the fields that follow in the same form.

Thanks!

Paul Newcome

INDEX/MATCH works similarly to a VLOOKUP but is much more flexible and less likely to break. There currently isn't a way to create an automatically dynamic dropdown like you are wanting though. The user would need to manually enter the request id into the second form, but then formulas would be used to pull the data from the new form submission into the original row.

Walid Sulayman

Oh I see! Thanks for your clarification. I was trying this with VLookup initially. Index/ Match works better, I agree. The main thing is to have a reference column from the first sheet feed automatically to the second one. I tried to simply refer the cells (=sheet1/requestID) and drag down in sheet 2. It kinda works, but not sure if it will be reliable on the long run. You always need to drag down further ahead so they automatically update from the first sheet. I am not sure if you have tested this.

Paul Newcome

@Walid Sulayman I am not sure what you mean by:

"You always need to drag down further ahead so they automatically update from the first sheet. I am not sure if you have tested this."

Margaret McKnight

Interesting thread but I am not sure I fully understand. In my scenario, I have a subset of items that require a weekly update. Currently, they are using a field identified as 'weekly update'. What I am really looking for is a form where they simply add the weekly update for the line item and it puts it into the comments with the user and date stamp. My goal is to then pull the most recent comment into a report for that small subset. Is it possible to set a form up to send the input directly to the comments?

Brandon Pinsky

@Paul Newcome is there any sort of dynamic dropdown feature coming like the one you described above where a form could be created to select a value from an existing sheet? Thanks!

Paul Newcome

@Brandon Pinsky I haven't heard of anything.