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!

Find more posts tagged with

Workflows in Smartsheet

Accepted answers

Javier Arbelaez

I don't know that yet. Maybe 10-20.

Paul Newcome

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

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.

Javier Arbelaez

Interesting. Ok I think I got that. Let me try it out. Thank you!

All comments

David Tutwiler

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.

Javier Arbelaez

OK thanks for your help.

Paul Newcome

What is the maximum number of rows you would anticipate needing to copy at any one given time?

Javier Arbelaez

I don't know that yet. Maybe 10-20.

Paul Newcome

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?

Andrée Starå

Hi @Javier Arbelaez

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!

Javier Arbelaez

@Paul Newcome It can go to the bottom of the sheet.

Javier Arbelaez

@Andrée Starå I am not understanding what you are trying to do with the helper sheet. Can you screenshot an example?

Paul Newcome

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

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

Javier Arbelaez

Interesting. Ok I think I got that. Let me try it out. Thank you!

Andrée Starå

@Javier Arbelaez

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée

Javier Arbelaez

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!

Andrée Starå

@Javier Arbelaez

No worries!

You're more than welcome!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.