Copying Multipe Rows to a New Sheet based on the Number entered into one cell from the master sheet
Hi Everyone,
I am trying to create a copy workflow where the value of 1 cell in a particular row will let the system know how many copies I need in my new sheet. For example, My master sheet will have columns for company, department, location, etc. and another column which says "How many employees". If I enter in 5 into that "How many employees" column, in my new sheet, I want 5 rows that repeat the same company, dept, location, etc. and then I can fill out employee info in that new sheet. Does anyone know if that is possible? Thanks!
Best Answers
-
I don't know that yet. Maybe 10-20.
-
Here is what I am thinking...
Create a sheet that mirrors the sheet you want to actually work in. Add in a text/number column and a checkbox column.
In the working sheet you are going to want to insert a text/number column that will replicate the row number using
=COUNTIFS([Column Name]$1:[Column Name]@row, OR(@cell = "", @cell <> ""))
Next we go back to the copy sheet. In the extra text/number column you are going to manually enter the numbers 1 - 20
1
2
3
4
5
etc...
Next we go into each of the columns you want to replicate the data in (including the column where you enter the number of employees and use an INDEX/MAX formula like so...
=IF([Number Column]@row <= INDEX({Working Sheet Number Of Employees Column}, MAX(Working Sheet Row Number Column})), INDEX({Working Sheet Column To Replicate}, MAX(Working Sheet Row Number Column})))
Then move over to the checkbox column and enter
=IF([Number Of Employees]@row <> "", 1)
Then set your automation to copy over any rows where the box is checked.
How this works...
You go to your working sheet and enter your row data including how many employees. The cross sheet references on the copy sheet pull this data over and will populate as many rows as you entered into the number of employees column. The checkbox will be checked for any rows that have text in the copy sheet which will trigger the automation.
You will need to save the working sheet once you enter the initial row, and you will probably want to refresh your browser, but it should automatically copy identical rows over to the working sheet.
-
Interesting. Ok I think I got that. Let me try it out. Thank you!
Answers
-
Unfortunately, I can't think of a way to do this. Copy is only one row at a time. I was trying to think of a way to duplicate the row once it hits the receiving sheet, but I still don't think there's a solution based around the currently available workflows/functions.
Sorry.
-
OK thanks for your help.
-
What is the maximum number of rows you would anticipate needing to copy at any one given time?
-
I don't know that yet. Maybe 10-20.
-
Ok. Would you need to copy them into a section in between other rows, or would it work for the new rows to populate at the bottom of the sheet?
-
I'm thinking about two ideas as a starting point.
- A so-called helper sheet with predefined rows for each option with a helper column with the number representing the number of rows that you want to copy over to the main sheet. We'd have another helper column with a checkbox that gets checked when the number is the one that is submitted from a form. Then we'd have a Workflow that copies the rows over.
- Helper sheet like above, but with parents/children for the options, but we'd only trigger the workflow for the parent, and the children rows would also be copied over.
Make sense?
Would that work?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Paul Newcome It can go to the bottom of the sheet.
-
@Andrée Starå I am not understanding what you are trying to do with the helper sheet. Can you screenshot an example?
-
Here is what I am thinking...
Create a sheet that mirrors the sheet you want to actually work in. Add in a text/number column and a checkbox column.
In the working sheet you are going to want to insert a text/number column that will replicate the row number using
=COUNTIFS([Column Name]$1:[Column Name]@row, OR(@cell = "", @cell <> ""))
Next we go back to the copy sheet. In the extra text/number column you are going to manually enter the numbers 1 - 20
1
2
3
4
5
etc...
Next we go into each of the columns you want to replicate the data in (including the column where you enter the number of employees and use an INDEX/MAX formula like so...
=IF([Number Column]@row <= INDEX({Working Sheet Number Of Employees Column}, MAX(Working Sheet Row Number Column})), INDEX({Working Sheet Column To Replicate}, MAX(Working Sheet Row Number Column})))
Then move over to the checkbox column and enter
=IF([Number Of Employees]@row <> "", 1)
Then set your automation to copy over any rows where the box is checked.
How this works...
You go to your working sheet and enter your row data including how many employees. The cross sheet references on the copy sheet pull this data over and will populate as many rows as you entered into the number of employees column. The checkbox will be checked for any rows that have text in the copy sheet which will trigger the automation.
You will need to save the working sheet once you enter the initial row, and you will probably want to refresh your browser, but it should automatically copy identical rows over to the working sheet.
-
Interesting. Ok I think I got that. Let me try it out. Thank you!
-
I saw that Paul answered already!
Let me know if I can help with anything else!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hey @Andrée Starå and @Paul Newcome, thank you for your help. Unfortunately, after reviewing with the requester, they realized they wanted to go a different way, so all this work for nothing /: but thank you for the new skill!
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.1K Get Help
- 414 Global Discussions
- 221 Industry Talk
- 460 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 461 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives