Is there a way to have a form update existing information?

MCWmbs

We are conducting an audit of our I-9s. In order to do so, we have pulled an active list of all of our employees (EEs). These EE names are already on the sheet.

In order to effectively complete the audit, there are specific things we need to check for based on the expiration date of the I-9 document that we have on file. The most practical way to make sure everything is captured, may be a form that we can use for each EE on our active list.

Is there a way for the information entered on the form to update on the existing row for that EE?

I was thinking of creating a report with the form and the active list showing, but thought that might be a little harder to digest.

Thoughts?

AdamApexConsultants

Of course - I have built similar solutions for other customers.

Source Sheet (where the data is stored) has columns Employee Email, Employee Name, and Job Title.
Target Sheet (where you want to pull data) also has an Employee Email column and needs to pull the Employee I9 information.

On the webform, ensure a required field is the unique match key (employee email) - that links the source and target data together

In your source sheet, build your index match formulas within their respective columns so when a webform submission is made on the target sheet, the index match will match the submission to the corresponding unique ID on the source sheet and populate the data.

Example: =INDEX({Source Employee Name}, MATCH([Employee Email]@row, {Source Employee Email}, 0))

ChristianFinke

@MCWmbs

You can update existing rows with form submissions in Smartsheet, but it takes a bit of a workaround. Make sure each employee has a unique identifier (like an employee ID) to match form entries to rows. Then, use Data Shuttle or an automation to update the right rows based on that ID. Alternatively, you could set up a report where team members can find and update rows directly, which might be simpler for real-time updates.

AdamApexConsultants

Do you have access to dynamic view by chance? This would be the most straightforward approach to update existing information based on a unique key (EEs).

Alternatively, you could leverage an index match formula to match the webform submissions to the the active EE list to match the values together

Feel free to send me a DM if you need additional help.

MCWmbs

@AdamApexConsultants I do not have dynamic view access, unfortunately. I was thinking of using the index match formula as I have used it before, just not with form submissions. Can you expound on the option to use a formula to achieve the desired result?

MCWmbs

@ChristianFinke I was thinking about using a report, but those reviewing the information may not find that as user friendly as I do. Can you explain the workaround a bit more? Is data shuffle an automation that I can use? Do you know off hand an automation that can achieve the desired result?

AdamApexConsultants

Of course - I have built similar solutions for other customers.

Source Sheet (where the data is stored) has columns Employee Email, Employee Name, and Job Title.
Target Sheet (where you want to pull data) also has an Employee Email column and needs to pull the Employee I9 information.

On the webform, ensure a required field is the unique match key (employee email) - that links the source and target data together

In your source sheet, build your index match formulas within their respective columns so when a webform submission is made on the target sheet, the index match will match the submission to the corresponding unique ID on the source sheet and populate the data.

Example: =INDEX({Source Employee Name}, MATCH([Employee Email]@row, {Source Employee Email}, 0))

ChristianFinke

@MCWmbs

I get it—a report might not be as user-friendly for those reviewing the data.

I personally would use Data Shuttle. Data Shuttle is an add-on you can use to import and update data based on a unique identifier, like Employee ID or Email. You’d set up a form to collect updates, then use Data Shuttle to match each entry to the correct row in your main sheet and update the information.

If you don’t have Data Shuttle, another option you could try is setting up an automation to move new form submissions to a specific sheet and then using INDEX/MATCH formulas to pull updates based on a unique ID. This way, your main sheet pulls in the latest data automatically.

Let me know if that helps, if you'd like to explore getting Data Shuttle, or if you'd like help setting this up!