Auto-populate form responses from another sheet

jordanlamagna

Good morning!

I currently have an intake form for basic requests for one of my departments. I also have a separate intake form for larger projects that need project management involved. What my ideal situation would be is that when the status on the Basic sheet changes to something like "Needs PM", it would have an automation requesting the requester fill out the new form, which would be prefilled with some of the information from the Basic sheet (things like sales order, customer name, etc.). I'm not sure if this is possible. I also thought about a way to use Update Requests but couldn't think of how to make that work either. Any insight would be helpful! Thank you!

kowal

hi @jordanlamagna,

you would have to combine in your message the link to the form with the dynamic fields name like: app.smartsheet.com/b/form/6145a2c33c4e42e68bf53900e4a4845b?Rep={{datafromcell}}

so if you send such link in your message to anyone the Rep is in this case the name of the column and the {{}} will be the dynamic data that will be updated based on the row you are sending so if someone opens such link the data will be prefilled automatically and no manual work will be needed.

Hope it helps.

Paul Newcome

I would:

Create the helper sheet.

Create an intake form on the helper sheet.

Use the form URL from the helper sheet in a formula in the ORIGINAL sheet to create the prefilled form URL.

Send the link from the original sheet.

Using the form URL from the helper sheet means that submissions go there automatically. Putting the formula to generate the pre-filled URL on the original sheet ensures that the fields are in fact pre-filled with the correct data from the original submission that the new submission is being based on. No cross sheet references or anything like that are needed.

kowal

hi @jordanlamagna,

yes it's possible you need to use this trick:

https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values

bsed on that you can send to someone link to the form that will be alrady prefilled.

hope this helps.

jordanlamagna

https://community.smartsheet.com/discussion/comment/477961#Comment_477961

I'm not sure this will work for me. Wouldn't I have to manually put default values? I want the fields to auto-populate with whatever is in the cell on the Basic sheet, which are always different.

Andrée Starå

https://community.smartsheet.com/discussion/137690/auto-populate-form-responses-from-another-sheet

Hi,

I hope you're well and safe!

You can use the URL-Query feature combined with cross-sheet formulas VLOOKUP or INDEX/MATCH to connect the sheets and automatically add the information.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Awesome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

jordanlamagna

https://community.smartsheet.com/discussion/comment/477994#Comment_477994

Just so I'm understanding correctly, these are the steps I would take?

• Create a helper sheet that uses functions to pull the necessary information from the Basic sheet
• have a column on said helper sheet that would create this URL using a CONCAT formula or something similar
• Set up an automation that would send a message with this URL to the appropriate person

I just want to make sure I'm understanding correctly. Thank you! :)

kowal

hi @jordanlamagna,

you would have to combine in your message the link to the form with the dynamic fields name like: app.smartsheet.com/b/form/6145a2c33c4e42e68bf53900e4a4845b?Rep={{datafromcell}}

so if you send such link in your message to anyone the Rep is in this case the name of the column and the {{}} will be the dynamic data that will be updated based on the row you are sending so if someone opens such link the data will be prefilled automatically and no manual work will be needed.

Hope it helps.

Paul Newcome

I would:

Create the helper sheet.

Create an intake form on the helper sheet.

Use the form URL from the helper sheet in a formula in the ORIGINAL sheet to create the prefilled form URL.

Send the link from the original sheet.

Using the form URL from the helper sheet means that submissions go there automatically. Putting the formula to generate the pre-filled URL on the original sheet ensures that the fields are in fact pre-filled with the correct data from the original submission that the new submission is being based on. No cross sheet references or anything like that are needed.