If contains & Index Collect Formula
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.
Answers
-
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)
-
@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))
-
@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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!