Referring to text field from another worksheet conditional on value of another cell

I'm making a project tracking sheet that contains several projects and tasks/actions with the assigned person to do them. I want to make a short/summary list of actions each person has assigned to them and display it on a separate sheet.

For example, say Sheet 1 has a list of actions in A1:A10, and a list of names of people assigned in B1:B10. In Sheet 2, I want to display all of the tasks where column B is "Kevin", and have them display as a contiguous list under a "Kevin's Actions" header.

I don't want to simply do this through filtering in Sheet 1 because there's a lot more information in Sheet 1 than I describe.

Thanks in advance. I've googled around a bit and searched the forums but haven't found a solution.

If you have people's names in row 1 starting with "Kevin" in A1 of Sheet2, then try this:

=IFERROR(INDEX(Sheet1!$B$2:$B$100,SMALL(IF(Sheet1!$A$2:$A$100=B$1,ROW(Sheet1!$A$2:$A$100)-ROW(Sheet1!$A$2)+1),ROWS(B$2:B2))),"")

Adjust ranges to suit (don't use whole columns) and then confirm with CTRL+SHIFT+ENTER not just ENTER, then copy across for the other names.

Thanks and example.

View attachment task-summary-test.xlsx

Thanks for the solid start, NBVC! I made one small change (specifically removing the $ in the second to the last "B2" reference, and it sort of works. See the attachment above. Sheet 1 has the list by tasks (with people assigned in B), and the Summary sheet is the summary I'm trying to produce. You can see that it works for Kevin's tasks, but not Matt's. What am I screwing up? I'm completely unpracticed with array formulas.

Thanks!

NBVC said:

If you have people's names in row 1 starting with "Kevin" in A1 of Sheet2, then try this:

=IFERROR(INDEX(Sheet1!$B$2:$B$100,SMALL(IF(Sheet1!$A$2:$A$100=B$1,ROW(Sheet1!$A$2:$A$100)-ROW(Sheet1!$A$2)+1),ROWS(B$2:B2))),"")

Adjust ranges to suit (don't use whole columns) and then confirm with CTRL+SHIFT+ENTER not just ENTER, then copy across for the other names.

Click to expand...

A couple of things.

The formula is a special Array formula. That means, as I mentioned, you need to confirm it with CTRL+SHIFT+ENTER not just ENTER. Then copy it down.

But before you do that, you need to make adjustments in the formula to suit your data layout...

So in A2, the formula would be:

=IFERROR(INDEX(Sheet1!$A$2:$A$100,SMALL(IF(Sheet1!$B$2:$B$100="Kevin",ROW(Sheet1!$A$2:$A$100)-ROW(Sheet1!$A$2)+1),ROWS(B$2:B2))),"")

Where "Kevin" can be replaced with an absolute reference to a cell containing that name., then it needs to be confirmed by holding the CTRL and SHIFT keys down and pressing ENTER. You should see { } brackets appear around the formula. Then you can copy it down.

Similarly in A8, form MATT.

=IFERROR(INDEX(Sheet1!$A$2:$A$100,SMALL(IF(Sheet1!$B$2:$B$100="Matt",ROW(Sheet1!$A$2:$A$100)-ROW(Sheet1!$A$2)+1),ROWS(B$2:B2))),"")

as per attached.