Help automating update request for an already submitted form

Kristi Grenda

Hello, All:

My company uses a form for QA submissions. My folks are asking for a way to edit the form after submission. I know that is not possible. I am looking for a way to edit the form after submission. I read through the community forums, and learned about a workaround that involved creating a second sheet that would link to a unique identifier and allow for a person to basically submit their own update request, as needed.

I created a second sheet (picture #2 below) with a form that simply asks the person to fill in the Row ID # for the line they want to update. That will then link to the Edit Request Cell Link with the formula: =INDEX([Row ID]@row, MATCH([Row ID]@row, {Row ID#}), 0) It worked the first time, but now I'm getting #INVALID VALUE on all other tests, and I do not know why.

The edit workflow trigger works fine. It simply checks if the Cell link and row ID match, checks the box, and sends the review an update request with the appropriate boxes from the form they filled out for them to update.

Any idea on why I'm getting an #INVALID VALUE and how to fix it?

Thank you!

Kristi Grenda

Kristi Grenda

After some help from the fabulous Smartsheet IT folks, I was able to get my workaround working! For those who might curious about the correct formulas:

=INDEX({Row ID#}, MATCH([Row ID]@row, {Row ID#}, 0)) - links the primary sheet to the secondary sheet to allow the respondent to trigger their own form update request

=IF([Edit Request Cell Link]@row = [Row ID]@row, 1, 0) - triggers the checkbox that activates the automatic workflow that sends the update request

Pics below if you want to see the workflow and form notification examples.

SmartLew

Hey!

If i have understood your goal correctly ,t he Index part of your formula is incorrect (in bold)

=INDEX([Row ID]@row, MATCH([Row ID]@row, {Row ID#})

This is currently referencing the same as your MATCH. It needs to be the value you are trying to retrieve from the other sheet.

so it would be something like

=INDEX(column from the other sheet), MATCH([Row ID]@row, {Row ID#},0)

Let me know!

Kristi Grenda

Good morning!

To clarify things a bit, the way the formula reads original has the MATCH part reading the column from the other sheet (bolded part is the link to the other sheet) and the INDEX is the primary sheet.

=INDEX([Row ID]@row, MATCH([Row ID]@row, {Row ID#})

I did give a try to the swapping the INDEX/MATCH to see what would happen, and that doesn't seem to be the issue. I got the following errors, even with playing with parentheses a bit to see if that might help.

=INDEX([Row ID]@row, {Row ID#}), MATCH([Row ID]@row, 0) Error #UNPARSEABLE

=INDEX([Row ID]@row, {Row ID#}, MATCH([Row ID]@row, 0)) Error #INVALID DATA

Today, I experimented with making it so that the form on the other sheet loads the requests to the top (instead of the bottom) of the sheet. BEHOLD! The INDEX/MATCH (initial formula) seems to work for the newest entry, but now, I get #NO MATCH for the other entries.

Any ideas on how to fix this? It seems to be trying to link to the cell, even if no form/request has been submitted. I might think an IFERROR would solve this, but there should be links for both IFO000003 and IFO00001, and there are not. Initially, when I made the 5th entry, it created an invalid link (there was not an edit request yet as it was just a QA form.) But, when I did create a new edit request at the TOP of the extra sheet, it linked and the #NO MATCH appeared for the other 2. The 3rd one has never linked properly.

Thanks.

Kristi

Kristi Grenda

After some help from the fabulous Smartsheet IT folks, I was able to get my workaround working! For those who might curious about the correct formulas:

=INDEX({Row ID#}, MATCH([Row ID]@row, {Row ID#}, 0)) - links the primary sheet to the secondary sheet to allow the respondent to trigger their own form update request

=IF([Edit Request Cell Link]@row = [Row ID]@row, 1, 0) - triggers the checkbox that activates the automatic workflow that sends the update request

Pics below if you want to see the workflow and form notification examples.