If the checkbox is checked copy the row

Question

I am having issues solving a problem.

I have a sheet containing multiple columns (3 text/number, 1 date, 1 contact list, and 1 checkbox). Made out of around 150 rows.

I want the rows copied to another sheet if the checkbox is checked.

I tried:

Using a copy row automation which works but rows are always copied at the end of the sheet, meaning that there are hundreds of empty cells and then the copied rows. In between two automation runs, again hundreds of empty rows.

I also tried using an INDEX(COLLECT which also works but since it is INDEX, it only returns the first row and not the whole list of items with an active checkbox.

Berbay · Answer

Berbay · Answer

Adding a new row with numbers worked perfectly and copying of rows to other sheet works with no issues. Thanks!

Adrien Leduc · Answer

Hi @Berbay the fact that you're getting hundreds of empty rows between 2 rows moved is not normal. when this happens this means these rows have been "edited" somehow. I would check the activity log to see what happens on your sheet.

Using the INDEX/COLLECT method could work, my advice there is to add an autonumber column, starting at 1, and run as many lines as you need (apparently 150, but feel free to go further for safety).

Then your INDEX/COLLECT formula would look like this:

=INDEX(COLLECT({Column to collect},{Column to Match},[Identifier to match@row),[AutoNumber]@row)

But perhaps there are other solutions, why are you copying the rows, what are you trying to achieve?