Two forms, one sheet moving responses to new cells

I have one sheet with two forms. It's for employee onboarding. One form records some information from the employee and one form records information from the hiring manager. Two new rows are created in the sheet once the forms are complete. I'm trying to link those form responses to other cells within the same sheet (in a column called Inputs).

I did add two checkbox columns with an automation so that when Form 1 is complete the box is checked, when Form 2 is complete the box in another column is checked. I thought this may help me create the formula so that the data is collected from the correct row.

I'm lost on what formula to use. INDEX/COLLECT, INDEX/MATCH?

Snip 1.PNG

Snip 2.PNG

Find more posts tagged with

Accepted answers

All comments

Ray Lindstrom

Hi @jjesmith,

Have you considered only using one form for the employee, and then using an Update Request Automation to collect the other field values from the hiring manager? That way your data will be on the same row. You could use formulas after two form entries, but the Update Request would make for a simpler and cleaner solution.

Have the Update Request trigger when a new row is added, and you can use the column value for "Hiring Manager" as the "Send to" email address.

Best of luck!

BRgds,

-Ray

jjesmith

Thank you. this sounds good. so like this: https://help.smartsheet.com/articles/2479266-automatically-request-updates-on-tasks

Then I can have the link to the 2nd form in the update request? then to link the cells just use regular cell linking because I know all data will be in Row 1?

Ray Lindstrom

@jjesmith,

In the Update Request setup, there is a section at the bottom called "Message includes:"

You want to choose "Specific fields", and select which fields need to be populated by the Hiring Manager. This Update Request includes it's own form which updates the same row. There's no need to build a second form. A second form would only complicate things because it would require linking.

The user who is sent the Update Request will receive an email, and part of that email, just below the body of the email, is a button called "Open Update Form".

jjesmith

I see. I will give this a try.

Then I can use regular cell linking since it will all be in Row 1, correct?

Ray Lindstrom

@jjesmith,

Not sure what you're trying to accomplish with cell-linking, but yes it would all be in 1 line and you could link to what you need.

The same form could be filled out by multiple employees, and each submission would create 1 line in the grid. And if there was a "Hiring Manager" column, then automation would run the Update Request for each new line seeking the Hiring Manager's input.

jjesmith

Thank you. With the cell linking I'm trying to move the important data to another column on the far left so it is each to see and reference rather than having to scroll all the way over to the far right to see what we need to get employee onboarded.

I plan to use this as a template and have a one sheet for each new employee. This sheet will then be referenced (and reminders/due dates initiated) by several departments to track completion of all tasks for each new employee.

jjesmith

Why do I need to select a field? can't it just be when a new row is added - period?

Ray Lindstrom

@jjesmith,

No, you do have to select a field. I would recommend choosing one that is a required field on the form, so that way you know it will always have a value added when the form is submitted. Whichever field you choose, then select "Any Value" in the "changes to:" section.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.