I need to create a row for multiple names recorded in a form and duplicate the other cells
I use a form for group training, where there are multiple fields to add names of employees, but all other fields have the same answer for everyone. How can I create a row for each name when a form is submitted and have every other column (that is not a name) just duplicate the original's value? Getting this result can be through a second sheet or even using a workflow if necessary.
I attached an example of a simplified form and what I want to get as an end result if that helps:
Best Answer
-
@WtaylorW do you have DataMesh? That would make it easy. However, you can set up another sheet to make this happen. I'll show you how to do this with one employee name -- just repeat for each column.
Create a workflow that's set up to copy a row to another sheet (Transfer Sheet 1) when the following is true:
- Name column is empty
- Employee Name 1 column is not empty
In Transfer Sheet 1, reflect the Employee Name 1 column into the Name column using this column formula:
=[Employee Name 1]
Set up a workflow in this sheet to immediately copy any added row back to your initial sheet. Test that to ensure it copies back with the Name column populated.
Troubleshoot the above to get this working. Now, the simplest way to get the other names to populate as a line is to repeat the above with a new transfer sheet for each name. You might be able to hack a different methodology, but there are multiple reasons this would likely backfire.
Answers
-
@WtaylorW do you have DataMesh? That would make it easy. However, you can set up another sheet to make this happen. I'll show you how to do this with one employee name -- just repeat for each column.
Create a workflow that's set up to copy a row to another sheet (Transfer Sheet 1) when the following is true:
- Name column is empty
- Employee Name 1 column is not empty
In Transfer Sheet 1, reflect the Employee Name 1 column into the Name column using this column formula:
=[Employee Name 1]
Set up a workflow in this sheet to immediately copy any added row back to your initial sheet. Test that to ensure it copies back with the Name column populated.
Troubleshoot the above to get this working. Now, the simplest way to get the other names to populate as a line is to repeat the above with a new transfer sheet for each name. You might be able to hack a different methodology, but there are multiple reasons this would likely backfire.
-
Thanks for the answer! I've seen others with the same or similar problems posted with no clear answers. This looks like it will work but it would also take a lot of time and additional sheets, since I have several of these forms.
I do have DataMesh but I have never taken advantage of it. I'm only somewhat familiar with it. Any pointers on how you'd set it up in this specific situation? Each time the form is submitted it will have different names under the columns so how would you recommend I configure that?
-
Hi @WtaylorW, the DataMesh explanation started getting a little long so I broke it off to a separate discussion, which you can find over here: Using DataMesh to Split One Line Onto Multiple Lines (for Forms Entries and Other Uses) — Smartsheet Community
Help Article Resources
Categories
Check out the Formula Handbook template!
