Best method for linking 2 sets of data together that use forms to collate the information?

SteCoxy

I'm working on a project to help devise a solution to track all of our organisation's skills & talent initiatives, plus that will provide us with some useful metrics.

The plan is have 2 forms - part 1 that collates all of the schemes' information and then a part 2 that collates information on outputs, but which is sent annually or when a scheme closes.

I've looked into the best way of doing this, as I'm conscious there's no immediate way of linking the data sets together i.e. they won't appear on the same row without a loss of functionality (using update requests - you lose the benefits of a form) or downsides (data on different rows, or in a different sheet, need to link them together etc).

Having looked at the Community forums, there seems to be various data linking strategies - including using 2 separate forms/sheets for the collation of data; using a custom form URL string; perhaps a copy/move automation into helper sheets and then in a "summary sheet" using INDEX/MATCH to pull together the 2 sets of data:
https://community.smartsheet.com/discussion/88035/how-can-i-link-two-forms-from-two-different-sheets-to-put-the-answers-on-one-singular-sheet
https://community.smartsheet.com/discussion/139333/issue-with-smart-sheets-forms-automation-and-data-not-being-recorded-in-the-right-row
https://community.smartsheet.com/discussion/138661/send-custom-form-links-using-automation

The unique identifier/common denominator would be the name of the scheme in each sheet. Is it possible using INDEX/MATCH or VLOOKUP, which has a helper column referencing the unique identifier, to pull the data from sheets 1 and 2 into a roll up sheet that has all the columns from sheets 1 and 2, without having to have formula references in each column?

If not, just wondering the best way to achieve what I need?

I'm also wondering if the new Column Links functionality could assist with this? Got a feeling it might not do as I read there might be a limit of 20 links?

jmyzk_cloudsmart_jp

Hi @SteCoxy

I wanted to share a method I’ve been using to combine data from more than two Smartsheet forms (on separate sheets) into a single report that allows both form-style input and consolidated viewing/editing—without relying heavily on formulas like INDEX/MATCH or losing form functionality through update requests.

https://app.smartsheet.com/b/publish?EQBCT=d1bf1164e4eb457a973c34c7ee9fb0b2

Example organization tracks initiatives with:

• Form 1 (Sheet 1): Scheme Info (e.g., name, budget, manager, etc.)
• Form 2 (Sheet 2): Scheme Outputs (e.g., participants, success rate, etc.) — filled later or annually

We needed a way to:

• View both sets of data in one place
• Edit rows from either sheet
• Maintain form-based user input (not just grid editing)
• Avoid complex cross-sheet formulas

The Solution: Smartsheet Report

Step 1: Keep Each Sheet Focused
Each form submits to its own sheet. We use a common identifier (Scheme Name) to link the records logically.

Step 2: Build a Report
Create a row report that:

• Pulls rows from both sheets
• Displays relevant columns side-by-side (start date, budget, success rate, etc.)
• Groups by “Scheme Name” to organize them visually
• Optionally adds columns like “Sheet Name” or “Row ID” to clarify data origin

Step 3: Enable Editing
As long as users have edit access to both source sheets, they can edit values directly in the report. This avoids the need for update requests.

Step 4: Add “Add New Item” Column for Easy Form Access
In each source sheet:

1. Create a sheet summary field called FormURL with the direct link to that sheet’s form.
2. Add a new column (e.g., “Add New Item”) with this column formula: =FormURL#
3. Include this column in your report so users can always open the form to add new data.

https://app.smartsheet.com/b/publish?EQBCT=253408206cf74461a5557f2fa674f74d

This makes it easy for users to enter new records using the intended form interface, even from the report.

Rows are grouped by Scheme. Sheet 1 and Sheet 2 rows are listed together. The user can edit directly or use the “Add New Item” links to open a pre-filled form.

Key Benefits

• No need to merge data into a third sheet with formulas
• Preserves form UX via direct form link
• Fully editable from the report (no update request needed)
• Easy to expand or maintain

SteCoxy

https://community.smartsheet.com/discussion/comment/487428#Comment_487428

Thank you kindly for this and I think this will certainly help me in future with other configurations, but sadly this doesn't quite fit what I need.

The solution consists of forms that will be completed by external partners/scheme suppliers, but who won't have access to the source sheets, as these will be accessed by internal staff only for managing the data and aggregation/reporting purposes.

What I need is when part 2 form is completed that it matches up to part 1 form's data - ideally needing this to be on the same row, so whether that's in part 1 form's associated sheet, or in a third sheet where both sets of data live in a consolidated view.

Is that possible? And if so, what would be the most efficient/streamlined way of doing it? Is it INDEX/MATCH or could this be achieved with Column Links functionality do you think?

SteCoxy

any thoughts on my comments above @jmyzk_cloudsmart_jp and/or anyone else who might have any suggestions around best way to achieve my requirements?

jmyzk_cloudsmart_jp

 Could you provide sample screenshots of what you were referring to in your post? Without them, it's hard to understand.

SteCoxy

Yes of course.

Part 1 form is here: https://app.smartsheet.com/b/form/1070033825d4401e8420077856759e7f - this acts as the method of collating all the initial data around the schemes and partnerships.

Then the aim is to follow this information up with a part 2 questionnaire to obtain metrics on outputs of those schemes. With it not being immediately possible to achieve this without losing functionality - opting for update requests (not possible to put full length questions or help text), for example - I've had to create a separate sheet and form: https://app.smartsheet.com/b/form/56715f58f1a64863a9308f0b3a0857f3

The team who will be managing the data want to save efforts/time by having this data consolidated together, rather than having to try and manually match parts 1 and 2 together by copying and pasting.

Any suggestions on the optimal way of doing this will be much appreciated.

jmyzk_cloudsmart_jp

@SteCoxy – Thanks again for sharing your setup. Based on your form structure and screenshots, here’s an updated and practical approach that leverages the fact that [Scheme Name] is unique.

One-to-Many Structure Confirmed

From what I can see:

• Form 1 captures one-time scheme setup info.
• Form 2 can have multiple submissions per scheme (e.g., annually or per phase).

This makes the relationship:

One-to-Many
(One row in Form 1 → Multiple rows in Form 2)

Updated Formula-Based Solution (No Sheet 3 Needed)

If [Scheme Name] is unique in both sheets, you can pull all related values from Form 2 into Form 1 using COLLECT, JOIN, and SUMIF as shown below;

[Submission Date] =JOIN(COLLECT({Form2_Scheme_Outputs : Submission Date}, {Form2_Scheme_Outputs : Scheme Name}, [Scheme Name]@row ), CHAR(10))
[Completed apprenticeship] =JOIN(COLLECT({Form2_Scheme_Outputs : Completed apprenticeship}, {Form2_Scheme_Outputs : Scheme Name}, [Scheme Name]@row ), CHAR(10))
[Completed apprenticeship Total] =SUMIF({Form2_Scheme_Outputs : Scheme Name}, [Scheme Name]@row , {Form2_Scheme_Outputs : Completed apprenticeship})

Here, CHAR(10) is used to show multiple rows in a cell.

https://app.smartsheet.com/b/publish?EQBCT=246f914e16374167a80d1ee49bfc52e1

The far right two columns' data above is retrieved to the three columns below.

https://app.smartsheet.com/b/publish?EQBCT=5636fefe587842e4aaa954fe4e19a2d2

Benefits of This Setup

• Works even if multiple rows exist in Form 2 per scheme.
• Supports rolled-up metrics, such as totals or multi-line lists (e.g., all submission dates).
• Keeps your Part 1 sheet as the central view.

Optional Enhancements

• You can also use MAX(COLLECT(...)) to pull only the most recent value for a field like completion rate.
• If you later need to limit or filter values (e.g., by year), consider adding a [Year] column to Form 2 and use it as an additional condition in COLLECT.

SteCoxy

wow.. thank you for such a detailed and helpful post! I'm going to give this a go and will report back to you on how I get on.