Using cross sheet reference to pull data from one column in Sheet 1 into multiple rows in Sheet 2

Lauren Dominique

I have two sheets.

Sheet 1 is essentially a directory. It lists all of the individual contacts that are involved in a project. Some columns in the sheet are are "Name" (Contact List), "Title" (Text), "Department" (Text), and "Completed Assignments" (Text).

Sheet 2 is where monthly assignments are recorded. The columns are "Assignment" (Text), "Assignee 1" (Contact List), "Assignee 2" (Contact List), "Month" (Text), and "Completed Date" (Date). I also have a column called "Month & Assignment" (Text) that combines the Month and Text fields. For instance, "February - Development" using the formula =[Month]@row + " - " + [Assignment]@row. In some instances, two individuals are assigned to one task; in others, there is only one assignee and "Assignee 2" is left blank. Each time a person has a monthly assignment, it is entered into a new row.

In Sheet 1, I want the "Completed Assignments" column to pull data from the Sheet 2 "Month & Assignment" column to populate a historical record of each person's completed assignments throughout the past 12 months. For example, if I had several assignments in Sheet 1 over the course of the past 12 months, my row in Sheet 1 would have the below in my "Completed Assignments" cell:

February - Development

March - Testing

April - Launch

I created a helper column in Sheet 2 that combined both assignees into one field (titled "Assigned People"): i.e., if Lauren was Assignee 1 and Kevin was Assignee 2, the helper column would list both Lauren and Kevin separated by CHAR(10).

The issue I am experiencing is when there are two assignees in one row of Sheet 2. I've tried a number of different formulas to no avail...

The first one I tried was attempting to reference each individual assignee in their respective columns using the OR & CONTAINS functions:

=JOIN(DISTINCT(COLLECT({Month & Assignment}, {Assigned People}, OR(CONTAINS(Name@row, {Assignee 1}), CONTAINS(Name@row, {Assignee 2})), {Date}, @cell > TODAY() - 365)), CHAR(10))

The above formula did not return any errors, but didn't pull any of the data; all fields were blank.

When that didn't work, I tried this:

=JOIN(COLLECT({Month & Assignment}, {Assigned People}, Name@row, {Completed Date}, @cell > TODAY() - 365), CHAR(10))

The above formula also doesn't return any errors, but it only works when there is only one assignee in the Sheet 2 "Assigned People" column. When there are two assignees, the "Completed Assignments" field remains blank for that person's row in Sheet 1.

I've even tried starting the formula with an IF statement:

=IF({Assigned People}, OR(CONTAINS(Name@row, {Assignee 1}), CONTAINS(Name@row, {Assignee 2})), JOIN(COLLECT({Month & Assignment}, {Assigned People}, Name@row, {Completed Date}, @cell > TODAY() - 365), CHAR(10)))

The above formula returns an #INVALID DATA TYPE error. So I tried two other variations...

=IF(OR(CONTAINS(Name@row, {Assignee 1}), CONTAINS(Name@row, {Assignee 2})), JOIN(COLLECT({Month & Assignment}, {Assignee 1}:{Assignee 2}, Name@row, {Completed Date}, @cell > TODAY() - 365), CHAR(10)))

=IF(CONTAINS(Name@row, {Assignee 1}:{Assignee 2}), JOIN(COLLECT({Month & Assignment}, {Assignee 1}:{Assignee 2}, Name@row, {Completed Date}, @cell > TODAY() - 365), CHAR(10)))

But, both of these return a syntax error...

Can anyone out there help me solve this?

Kudos to you for reading through this lengthy (and possibly confusing...) post, and thanks in advance for any insights you can provide!

Lauren Dominique

Answering my own post because I finally figured it out with help from @Scott Orsey outside the community forum.

The solution required first changing the "Assigned People" column in Sheet 2 to have multi-select drop-down properties.

Then, the formula for the "Completed Assignments" column in Sheet 1 was: =JOIN(COLLECT({Month & Assignment}, {Assigned People}, HAS(@cell, Name@row), {Completed Date}, @cell > TODAY() - 365), CHAR(10))

Thanks, Scott!