Make Single Line Form Entry into 18 Separate Rows

kcaudill1989

I have created a process using Helper Columns, Formulas and Automation that takes a 1 row form entry copies it into 18 separate rows based on the Sport Column and then copies those 18 rows into a 2nd sheet data sheet and then moves the entered row (Row 1) to an archive sheet.

The theory was that when someone submits a form response it is entered in Row 1of the (Response Sheet), then the formulas copy the information to the separate rows below Row 1 and then the automation copies the 18 rows to the 2nd sheet (Data Sheet) and the another automation moves Row 1 to an Archive Sheet.

This is the problem. It seems that if too many responses are submitted to close together then it doesn’t have time to complete the process and therefore some submissions don’t go through the whole process.

Does anyone have an idea of how I make this work for all submissions or how else I could accomplish this task.

Here are the formulas I am using:

To make one row into 18 separate rows by sport I am using the following cell formulas in 18 rows, I have cell formulas for each column that needs to be copied to the Officials Reg Numbers – Data Sheet.

Submission Number - =IF(Officials@row <> "", MAX([Submission #]:[Submission #]))

Year – =INDEX([Form Year]:[Form Year], 1)

Name – =INDEX([Form Name]:[Form Name], 1)

Email – =INDEX([Form Email]:[Form Email], 1)

State – =INDEX([Form State]:[Form State], 1)

Sport – Hard coded with each sport there is a column for (Baseball, Basketball, etc.) and then used in Officials formula

Officials – =INDEX(Basketball:Basketball, 1)

Submission # - Hard coded with numbers 1-18

Then I have the below Column Formulas and Automation that work on Row #1, the row the submission is written to.

Helper Row:

=[Submission #]@row

Check Archive:

=IF(HAS({Officials Reg Numbers - Submission Number}, Helper@row), 1, 0)

Automation – Copy to Data Sheet:

Trigger - When rows are changed

When “Submission Number” changes to Any Value

AND Where “Submission Number” is not blank

Copy Rows to “Officials Reg Numbers – Data Sheet

Automation – Move Submission to Archive

Trigger – When rows are changed

When “Archive” changes to Checked

Move rows to Officials Reg Numbers - Archive

Here are a couple images of my sheets:

Submission/Processing Sheet (I have hidden the submission sport columns, except one) just to make the screenshot manageable.

Final Data Sheet

Andrée Starå

https://community.smartsheet.com/discussion/137106/make-single-line-form-entry-into-18-separate-rows

Hi,

I hope you're well and safe!

What about adding the form to a dashboard and having something show that it's ready again for submission?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

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

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me 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! I appreciate it, thanks!

kcaudill1989

Thanks Andree, unfortunately, it isn't the Form that is the issue. The issue is actually that the formulas and automation for the first submission, in some cases, doesn't run before the second submission comes in and pushed the 1st submission down to row 2.

Andrée Starå

https://community.smartsheet.com/discussion/comment/476038#Comment_476038

Happy to help!

I've developed a couple of different versions of similar processes for clients. I usually have a so-called helper sheet that gets a copy of the row from the submission and then triggers a copy of the rows to the final sheet.

Make sense?

✅ Remember! Did my comment(s) help/answer your question or solve your problem? Please support the Community and me 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! I appreciate it, thanks!