0

So what I am trying to do is copy cells from the "Tasks and Responsibilities" column that are next to the boxes that are checked, and paste them into another sheet.

For example: I want all of the tasks that are next to Dawn's checked boxes copied into the "Tasks DD" sheet, without any of the tasks are next to empty boxes, in order to make a separate sheet that only has Dawn's tasks listed. I know that I can do this manually if need be but I thought it would be cool to see if there was a formula for it.

I don't want to cut them, I want the original sheet to stay intact.

1

2 Answers 2

0

Use query() or filter(), like this:

=query('2022 Task Assignments'!A4:F, "select A where B = true", 1)

Alternatively, use filter views to let users decide which rows to show at a time. This feature lets multiple simultaneous users sort, filter and edit the sheet without disturbing each other. This would let you avoid scattering data across many tabs.

For additional ease of use, you can insert links in the frozen section of the sheet to easily switch between filter views, instead of having to go to Data > Filter views to switch. See the Filter views example spreadsheet for an illustration.

2
0

Dynamically Generate by Matching Field

Example using a dynamic match on employee name.

Assuming:

  1. You put the employee name(s) in cell 'A1' of your new filtered sheet(s)
  2. You name your source range 'rngTasks' ('2022 Task Assignments'!$A$2:$F$17)
  3. You put the following formula in 'A2' of the new sheet.

Your formula would look like this:

=query({rngTasks},"select Col1 where Col"&match($A$1,index(rngTasks,1)&" matches TRUE",0)

FYI I named the range to make the formula shorter and easier to read, but it will also have other benefits when you expand the range etc.

Dynamically populate the employee list

With this approach you can actually dynamically select the employee as well from the data set and put everything in one sheet additional should it be useful to you.

Assuming:

  1. You name your source range 'rngTasks' ('2022 Task Assignments'!$A$2:$F$17)
  2. You name your row with employee names 'rowEmployee' ('2022 Task Assignments'!$A$2:$F$2)
  3. You add Data Validation to cell 'A1' of your new sheet to create a self populating drop-list ('List from a range' with your 'rowEmployee' range as the source). enter image description here
  4. You put the same formula in 'A2' of the new sheet

Now when you drop the menu in cell 'A1' of your new sheet the employee will change with your selection.

=query({rngTasks},"select Col1 where Col"&match($A$1,index(rngTasks,1)&" matches TRUE",0)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.