Table from Task sheet | ||||||||||||||||||||
A | B | C | D | E | F | G | H | W | X | Y | Z | AA | AB | AC | AD | AE | AH | |||
2 | ID | Project Name | Task ID | Task Description | Task Type | Start Date | End Date | # of Days | Resource 1 | Resource 1 Contact | Resource 2 | Resource 2 Contact | Resource 3 | Resource 3 Contact | Resource 4 | Resource 4 Contact | Resource 5 | Resource 5 Contact | ||
3 | 1 | Project 1 | Task A | Task a is | Main | 1/20/17 | 1/20/18 | 365 | Ana | Ana@gmail.com | Rob | Rob@gmail.com | Bill | Bill@gmail.com | Jan | Jan@gmail.com | Sara | Sara@gmail.com |
<tbody>
</tbody>
Table Array Formula:
=IF(ROWS(C$7:C22)>$C$2,"",INDEX(Tasks!B$3:B$2012,SMALL(IF(Tasks!$U$3:$AH$2012=$B$2,ROW(Tasks!$U$3:$AH$2012
)-ROW(Tasks!$U$3)+1),ROWS(C$7:C22))))
Sheet with Table Array(Resource View)
B | C | |
1 | Resource | Count |
2 | Ana | 6 |
<tbody>
</tbody>
PROBLEM: I can only pull data that matches with 1 column of resources. I want to be able to have the data fill if the resource name is in any of the resource columns. For example if Ana is in the Resource 2 column for project 1 and resource 3 column for project 4, I want both entries to appear.