Copy data entered on secondary sheets to "primary" sheet.

Question

We are setting up a project approval process. We have a primary “Intake” sheet where initial project data is entered. In many cases, users will be providing project budget and resource information later. We do not want them entering data directly on the sheet so would like to use supplemental forms.

My understanding is that if users input additional data from a second form to the original intake sheet it will create a new row – is that correct? If so, we will be creating additional forms/sheets for that budget and resource data input.

If we do that, how do I know when data is entered into the supplemental sheets and how do I pull that data into the main “Intake” sheet?

Once all the data is collected on the main Intake sheet, we will kick off an approval workflow.

Any help or suggestions would be greatly appreciated!

SteveE · Answer

Genevieve,

Thank you for the feedback.  We have moved away from multiple forms and are instead working on a Project Intake sheet where users can update the data directly and then once the project is approved, copying some of that data to an Approved Project sheet.  I submitted another related question just a few minutes ago (before I saw your response) as I am still stuck.  I am getting better at formulas, but still have quite a ways to go. 😕

https://community.smartsheet.com/discussion/90295/copy-certain-data-to-secondary-sheet-based-on-condition#latest

Genevieve P. · Answer

Hi @SteveE

You are correct that  Form submissions will create new rows per-submission.  (See: Collect Information with a Form)

An alternative would be to send out Update Requests from the main source sheet.

An Update Request would send just that one row to a specific person in a "form" format, allowing them to update certain fields from their email (without needing access to the source sheet in Smartsheet) which would directly update the source sheet. You can manually send an Update Request or set up an Automated Workflow.

If you lock columns in the sheet, these fields would appear as Read Only in the update form, or you could exclude fields all together.

Otherwise, yes, you could have a secondary sheet where a form submits new rows. In this instance you would need some sort of unique identifier across sheets (e.g. project name or project number) that a formula can use to match rows and bring back specific information. This would be done via either an INDEX(MATCH formula or INDEX(COLLECT. Here are some other Community posts that discuss this type of formula:

* Need a Clear Example of Using INDEX and MATCH in lieu of HLOOKUP
* Index Collect

Cheers,

Genevieve