Copy rows to another sheet multiple times

Laura ZJ

I have a sheet in which staff can enter their upcoming PTO with a start and end date. I want to view in calendar view with individual entries on each date, not one bar that spans across multiple dates. Is there a way to copy a row to another sheet multiple times based on the number of workdays between start and end date? I don't want to have the notation on weekend days.

Another reason I want to copy to another sheet is I want to calculate the number of people gone on each date (to be able to manage to a threshold). I can't figure out how to do that just on a start and end date that covers multiple dates.

Georgie

Hi @Laura ZJ,

You could do this with a number of helper columns and multiple copy row workflows - here’s what I would do:

1. Create a “Workdays” column with the formula =NETWORKDAYS([Start Date]@row, [End Date]@row)  - change your column names as required, but this will calculate the number of working days between the Start Date and End Date. Convert this to a column formula so it applies to all rows.
2. Add multiple checkbox columns which you’ll use as triggers for your automations. We can use each column to copy a row once, and our formulas will ensure that boxes are checked for every column where necessary (e.g. if Workdays is 2, the "Copy row" and "Copy row x2" columns should be checked, so that the row will be copied twice).
   1. In your first checkbox column - I’ve called mine “Copy row”, you can use the column formula =IF(Workdays@row >= 1, 1).
   2. In the next checkbox column (e.g. “Copy row x2”), use the formula =IF(Workdays@row >= 2, 1).
   3. Keep adding as many columns as you need (e.g. if maximum number of workdays is likely to be 10 workdays, add 10 columns), incrementing the number in the formula each time.
3. Once you have all your helper columns set up, create multiple automations to copy the row to the destination sheet - each automation will trigger off of a different “Copy row” column.

So, your original sheet might look something like this: 

Then, your automations will look similar to below:

As I’ve added 3 checkbox columns, I’ve created 3 automations:

This results in each row being copied to the destination sheet the same number of times as there are working days between the start and end date for that row - you can see my destination sheet below:

You can then hide columns in your destination sheet and sort it as desired.

Then, when you switch to calendar view in the destination sheet, you’ll see a task bar for each row, so calendar view of my destination sheet looks like this:

Does that work for you?

Georgie

Georgie

Hi @Laura ZJ,

You could do this with a number of helper columns and multiple copy row workflows - here’s what I would do:

1. Create a “Workdays” column with the formula =NETWORKDAYS([Start Date]@row, [End Date]@row)  - change your column names as required, but this will calculate the number of working days between the Start Date and End Date. Convert this to a column formula so it applies to all rows.
2. Add multiple checkbox columns which you’ll use as triggers for your automations. We can use each column to copy a row once, and our formulas will ensure that boxes are checked for every column where necessary (e.g. if Workdays is 2, the "Copy row" and "Copy row x2" columns should be checked, so that the row will be copied twice).
   1. In your first checkbox column - I’ve called mine “Copy row”, you can use the column formula =IF(Workdays@row >= 1, 1).
   2. In the next checkbox column (e.g. “Copy row x2”), use the formula =IF(Workdays@row >= 2, 1).
   3. Keep adding as many columns as you need (e.g. if maximum number of workdays is likely to be 10 workdays, add 10 columns), incrementing the number in the formula each time.
3. Once you have all your helper columns set up, create multiple automations to copy the row to the destination sheet - each automation will trigger off of a different “Copy row” column.

So, your original sheet might look something like this: 

Then, your automations will look similar to below:

As I’ve added 3 checkbox columns, I’ve created 3 automations:

This results in each row being copied to the destination sheet the same number of times as there are working days between the start and end date for that row - you can see my destination sheet below:

You can then hide columns in your destination sheet and sort it as desired.

Then, when you switch to calendar view in the destination sheet, you’ll see a task bar for each row, so calendar view of my destination sheet looks like this:

Does that work for you?

Georgie

Laura ZJ

Thank you, Georgie. This worked perfectly!

Georgie

Hey @Laura ZJ,

Glad I could help! Thanks for confirming it worked for you 😃

Georgie