Best Of

@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.

Hi @ctaft

It sounds like you are almost there.

How about using this to extract the month:

=LEFT([Audit Date]@row , 2)

It means look at Audit Date and return the first two characters.

Then for the year:

=MID([Audit Date]@row , FIND("/", [Audit Date]@row ) + 4, 2)

This means look at Audit Date, FIND the first /, count 4 characters from there (with the / being 1, mm being 2 and 3, and / being 4), return the next 2 characters.

You would then combine these to get the format you want:

="20" + MID([Audit Date]@row , FIND("/", [Audit Date]@row ) + 4, 2) + " / " + LEFT([Audit Date]@row , 2)

Hope this helps.

Hi Sharon,

My issue was resolved, and everything is now working as expected.

To fix the problem, I replaced the TODAY() function in all relevant formulas with a reference to a new column titled [ Schedule daily sheet save ].

Steps Taken:

1. Added Column:
   Name: [ Schedule daily sheet save ]
2. Created Automation:
   Name: Schedule-daily-sheet-save
   Trigger: When a date is reached – every day starting on [set date/time]
   Condition: Where Schedule daily sheet save is in the past
   Action: Record a date in Schedule daily sheet save

Formula Update Example:
Old: =IF(AND([% Complete]@row < 1, [Due Date]@row < TODAY()), 1)
New: =IF(AND([% Complete]@row < 1, [Due Date]@row < [Schedule daily sheet save]@row ), 1)

Let me know if this works.