Pulling Data from Smartsheet with a form to another Smartsheets

SarahCally

Hi,

Hope someone can help!

I have a form set up on one smartsheet (1) which is to be sent to new onboarding colleagues to get some information. When the fill this out this auto-populates the rows in that smartsheet (1) which is fine.

But I have their names listed on another smartsheet (2).

Once they have filled in the info on the form from smartsheet (1), and selected their name from the drop down, I need all data to then transfer to the other smartsheet (2).

Is there a formula for this?

Smartsheet (1)

Smartsheet (2)

I tried cell linking but it erases the names from Smartsheet (2) which I need to keep.

Thanks

Sarah

Andrée Starå

Hi @SarahCally

I hope you're well and safe!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet.

To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you think you need in the Destination sheet.

Another option could be to copy the row to the other sheet for each submission.

Would any of those options work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community 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!

SarahCally

Hi - Thank you for your reply. Hope you are well.

I've tried VLOOKUP and INDEX/MATCH but can't quite figure out the formulas.

Is this something you can help with?

Thanks

Sarah

Paul Newcome

Is there a reason you need this on a separate sheet instead of just creating a report?

SarahCally

Hi @Paul Newcome -

Thanks for your reply.

No reason.

What we want to do essnetially is to email new starters a form they need to complete and then have the fields drop onto a sheet which will already have their name and some other details on it.

If I do a form as normal it populates a new line when I need it to add to an pre-existing line.

If that makes sense?

The they select their name from a drop down on the form and then select the other details we need and it populates to the blank spaces:

Hope that makes sense!

Sarah

Paul Newcome

Have you looked into sending an Update Request instead of a Form?

SarahCally

Would that be from the workflow instead of a form?

Just I know my peers wanted it to have our branding included.

Thanks

Sarah

Paul Newcome

Ah. The branding would definitely be done through the form. I'm just trying to get a feel for the flow of things. You can use formulas to pull data across, but there are (at times) more efficient ways to capture the data. So do you already have data entered in Sheet 2 for the individual and then they fill out the form, or is the form filled out first and then you add data to their entry?

SarahCally

So my initial idea is for them to fill out the form on one sheet.

Then add a formula to another sheet to pull the relevant information over once it drops on the first sheet.

I don't know if this a very convoluted way of doing it but I trued a few others like linking and workflows and could not get the result.

I've played with formulas but can't quite get it. Get either 'unparsable' or 'circular reference'.

Thanks

Sarah

Paul Newcome

Why not just enter the additional data on the same sheet that the form is populating? Do you really need to have two separate sheets?

SarahCally

Hi,

When they submit the form it populates a new line - we need it to add to an existing line.

So in an ideal world they will select their name from a drop down on the form, then submit, and then it will populate the data to the line the where name is already on the form which matches what they chose on the drop down.

Thanks

Sarah

Paul Newcome

I'm sorry. I must be misunderstanding the workflow a little bit. Are they filling out the form first, or is the additional information being filled out first?

RPellegrino

Hi;

I am creating an Overtime OT sign up list. I want the form to employees to be only Name, date sign up and date for OT.

Once the employee has filled in their name and dates, I want SmartSheets to complete the rest of the columns info required, that is already set up on a separate employees data sheet.

I will require columns: seniority number, shift #, building code, job owned and classification to be completed in the sign up row for the employee, which should be pulled automatically from the employee list.

Is this a feature that SmartSheets can complete, using only the Name to pull other important info.

Paul Newcome

@RPellegrino You would use a series of INDEX/MATCH formulas based on the name in the sheet to pull the data over once the form has been submitted.