Complex Data transfer based on submission date

S_Ko

Hi there,

I'm working on a reporting & tracking system that was originally set up in MS Access. The current process is very manual for the Ops person, so I'm trying to see if SS's automations & forms will work.

Each team gains points throughout the year based on when things are submitted. As there are 30+ teams, I've created a "Submission" sheet with the forms I want them to complete or upload documents to. What is the best way to track each teams submissions dates (created date auto column) and then assign the points to that team (based on a matrix I'd need to build into a formula)?

Currently, my set up:

1. Submission form for all submissions
2. Team Tracking Template (one per team)- this is ideally where submission dates are tracked and then points are tallied automatically based on a formula I've created.
3. Chapter Portal, where all form links and metrics are on display for chapters to access.

Any ideas on how to best get data to transfer automatically would be great. I've looked into Data Mesh but as I'm working with Dates, it makes it difficult to configure one sheet to multiple different sheets.

Thanks!

Andrée Starå

Hi @S_Ko

I hope you're well and safe!

You could set up workflows to move the rows to separate team sheets.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

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!

S_Ko

Hi @Andrée Starå

I thought about this, but moving or copying a row would erase the formula I have already put together in the last column. I could move formulas to the sheet summary, but tracking the cell's it would go into would be difficult.

Is there a way to set up an automation trigger that can link cells vs rows?

My goal was to trigger: "Team name" to the appropriate team sheet and then submission content & date to appropriate row in that sheet.

It may be a lofty request.

Andrée Starå

@S_Ko

You could add column formulas to the team sheet.

Also, this might help.

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

Make sense?

Would any of those options work/help?

✅Remember! 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!

S_Ko

@Andrée Starå

The index/matching or vlookup would work... that said, I'm not the best with either of those formulas.

If I want to take the data on the submission form and copy over one cell how would I do that.

Example:

Submission for Report is submitted for Team X:

Data to reference: Team X, Form Submitted (Quarterly Report), Q1

Data I want form this row: Created Date (system column associated on the row of the entries above).

How would I write the formula so that it would filter the Data to reference and only show me the created date on my Team X Sheet? I really only care about whether the items were submitted and the date they were submitted. Our point system is based on this submission date.

Thanks!

Andrée Starå

@S_Ko

Do you have anything that we could use to MATCH the data between the sheets?

If not, we could add an auto number column on the main one and then add the number manually on the others.

Make sense?

Would that work?

S_Ko

@Andrée Starå

I have the sheets set up as:

Team Sheet:

Report | Task (Quarter) | Due Date (what I use for my point system) | Submission date

Submission Intake Sheet:

Chapter Name | Form Submitted/Report | Quarter | Created Date (system generated)

I can always create a column in the Team Sheet to use that as the MATCH. Or if possible, we could match by Report & Form Submitted.