If contains & Index Collect Formula

JM_D

I am new to Smartsheet. Top is my data set from another sheet, bottom should be the result that is located in another sheet. been trying to formulate but always end up with unparseable. I hope someone can help me. Thank you.

Samuel Mueller

This is one is fairly complicated, but you need a series of if statements and carefully selected cross reference ranges. You need a cross reference for each row/person in the other sheet from task1 to task3 columns. You also need a cross reference containing Task1-Task3.

Then a formula like this will go next to your person1,2,3 in the sheet you want to bring the tasks over..

=IF([Column5]@row = "Person1", INDEX([Task1]$1:[Task3]$1, 1, MATCH("yes", [Task1]$2:[Task3]$2, 0)), IF([Column5]@row = "Person2", INDEX([Task1]$1:[Task3]$1, 1, MATCH("yes", [Task1]$3:[Task3]$3, 0)), IF([Column5]@row = "Person3", INDEX([Task1]$1:[Task3]$1, 1, MATCH("yes", [Task1]$4:[Task3]$4, 0)))))

[Task1]$1:[Task3]$1 will be replaced with the cross reference containing the words task1-task3

[Task1]$2:[Task3]$2 will be replaced with the cross reference containing the nos and yess for person 1

[Task1]$3:[Task3]$3 will be replaced with the cross reference containing the nos and yess for person 2

[Task1]$4:[Task3]$4 will be replaced with the cross reference containing the nos and yess for person 3

If you actually have a lot of people, this would not be an easy task. or someone else may need to come up with a better way to do this.

[Column5]@row is where the "person1" is identified to bring over the task. (the bottom part of your image & mine)

Courtney Collier

@Samuel Mueller I am having a similar issue with a formula to populate the Final Walkthrough date (yellow below) I can get the index/match to work individually, however, I have not been successful at combining them into one formula using "IF". Thanks for taking a look!

=INDEX([Date 1 - Previous]@row, MATCH("Previous", [Current/Previous Month]@row))

=INDEX([Date 2 - Current]@row, MATCH("Current", [Current/Previous Month]@row))

Samuel Mueller

@Courtney Collier looking at this I would not use an index match. It will always just match the first "Previous", so if you have a "Previous" later in column with a different date it won't see that. What if you try something like

=IF([Date 1 - Previous]@row = "", [Date 2 - Current]@row, [Date 1 - Previous]@row)