Multiple Row Generation on a Single Form Submission

OSE Helpdesk

I have a form that includes a date range (e.g., February 1–5, 2025). I would like the system to generate multiple rows in the backend, corresponding to each day within the selected date range.

For example, if the date range is February 1–5, 2025, five rows should be created, each with the same details but with different dates (one per day from February 1 to February 5, 2025).

Is there a way to achieve this functionality in Smartsheet?

Paul Newcome

What is the maximum number of days that could be entered, and how is the range entered exactly?

OSE Helpdesk

Hi Paul, good morning. We are directly entering the data on the form. The maximum number of days is 10.

Andrée Starå

https://community.smartsheet.com/discussion/comment/474009#Comment_474009

Hi @OSE Helpdesk,

I hope you're well and safe!

I developed something similar for a client a while back, and it's pretty advanced.

Do you have any budget available to hire an Expert to develop it?

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!

Paul Newcome

@OSE Helpdesk You should be able to set this up yourself. First create the sheet that will have the form in it. Make sure new form entries are going to the top of the sheet. I will provide some steps and screenshots and then at the bottom of this comment will be a link to a similar thread to show the base functionality.

Then you will need to create some additional columns to allow for a "Formatting Section". The columns here would depend on exactly what you want copied from each submission, but going off of your screenshot, you should only need a date column and a text/number column for the actual data. We are also going to use an auto-number column, and another text/number column (there will be screenshots below).

The Description column is going to be the most straightforward formula.

=IF(Number@row <> "", INDEX([Order Description]:[Order Description], 1))

The Date column would have this in it:

=IF(Number@row <> "", IF(INDEX([End Date]:[End Date], 1) >= INDEX([Start Date]:[Start Date], 1) + Number@row - 1, INDEX([Start Date]:[Start Date], 1) + Number@row - 1))

And in the extra text/number column (called "Entry Number" in the screenshots) use

=MAX(Auto:Auto)

Set up a second sheet with the date column and description column, then create a Copy Row automation on the original sheet triggered when [Entry Number] changes to any value with the condition of the Date column not being blank and sending rows to the second sheet.

https://community.smartsheet.com/discussion/119380/split-multiple-selections-in-single-form-into-individual-rows-core-product/p1

Paul Newcome

@OSE Helpdesk You should be able to set this up yourself. First create the sheet that will have the form in it. Make sure new form entries are going to the top of the sheet. I will provide some steps and screenshots and then at the bottom of this comment will be a link to a similar thread to show the base functionality.

Then you will need to create some additional columns to allow for a "Formatting Section". The columns here would depend on exactly what you want copied from each submission, but going off of your screenshot, you should only need a date column and a text/number column for the actual data. We are also going to use an auto-number column, and another text/number column (there will be screenshots below).

The Description column is going to be the most straightforward formula.

=IF(Number@row <> "", INDEX([Order Description]:[Order Description], 1))

The Date column would have this in it:

=IF(Number@row <> "", IF(INDEX([End Date]:[End Date], 1) >= INDEX([Start Date]:[Start Date], 1) + Number@row - 1, INDEX([Start Date]:[Start Date], 1) + Number@row - 1))

And in the extra text/number column (called "Entry Number" in the screenshots) use

=MAX(Auto:Auto)

Set up a second sheet with the date column and description column, then create a Copy Row automation on the original sheet triggered when [Entry Number] changes to any value with the condition of the Date column not being blank and sending rows to the second sheet.

https://community.smartsheet.com/discussion/119380/split-multiple-selections-in-single-form-into-individual-rows-core-product/p1

jmyzk_cloudsmart_jp

Hi @OSE Helpdesk

You can achieve this functionality in Smartsheet by combining formulas and automation workflows.

Logic:

When a row is added to the Master Sheet, it is copied to Copier Sheets (1–10), where a Sheet_ID(1-10)-based formula generates individual dates within the selected range, and rows with valid dates are then automatically copied to the Final Target Sheet.

https://app.smartsheet.com/b/publish?EQBCT=71626c70ec1f45fc8e7651b6cb8565ab (Master Sheet)

https://app.smartsheet.com/b/publish?EQBCT=132afc4383ec4b97a1237db8d63c8784 (Copier Sheet Example)

Steps:

1. Copy Row to Copier Sheets (1–10) When a Row is Added to the Master Sheet:
   • Set up an automation rule in the Master Sheet(e.g., "136425 Multiple Rows on a Form Submission"):
     • Trigger: When a row is added.
     • Action: Copy the row to Copier Sheets 1–10.
2. Copier Sheets (1–10) Setup:
   • Each Copier Sheet has a Sheet_ID field (e.g., Copier 4 has Sheet_ID = 4).
   • This determines whether to calculate a date or leave it blank.
   • Formula to Generate Dates:
     • [Date] =IF(End@row - Start@row + 1 >= [Sheet_ID]#, Start@row + [Sheet_ID]# - 1)
     • If the Sheet_ID falls within the range, it calculates a date. Otherwise, it remains blank.
3. Automation to Copy Rows to the Final Target Sheet:
   • Set up an automation rule in each Copier Sheet:
     • Trigger: When rows are added or changed.
     • Condition: If the Date column is valid (not blank).
     • Action: Copy the row to the Final Target Sheet.

Result:

Each selected date gets a separate row in the Final Target Sheet.

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

An interesting behavior of the automation is although I set the copy row automations in the Copier 1 to 10's order, the copied sheets' dates are not necessarily in that order.

jmyzk_cloudsmart_jp

You can achieve this functionality in Smartsheet by combining formulas and automation workflows.

When a row is added to the Master Sheet, it is copied to Copier Sheets (1–10), where a Sheet_ID(1-10)-based formula generates individual dates within the selected range, and rows with valid dates are then automatically copied to the Final Target Sheet.

jmyzk_cloudsmart_jp

Sheet Structure

Master Sheet Example

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

Copier Sheet Example

[Date] =IF(End@row - Start@row + 1 >= [Sheet_ID]#, Start@row + [Sheet_ID]# - 1)

Workflow Automation Example of Copier Sheet

Target Sheet Example

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

Paul Newcome

@jmyzk_cloudsmart_jp If you follow my above outline, you shouldn't need to create that many additional sheets. You should only need the one that houses form submissions and the "final".

jmyzk_cloudsmart_jp

Thanks, @Paul Newcome! Your solution works perfectly and has saved us a lot of time. Keeping it to just one form submission sheet makes everything much more efficient. Appreciate your insight!😁

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