Formula Referencing from Another Sheet

Smartsheet Community I have a 2 difficult formula that I am trying to convert from Excel to Smartsheet.

Formula Conversion (1)

Excel Formula: This Formula Yields a Result of the Sprint Task 2: ACR-34

=IFERROR(INDEX(Sprints!$A$2:$A$210,MATCH(D6,Sprints!$G$2:$G$210,0),1)&INDEX(":"&Sprints!$B$2:$B$210,MATCH(D6,Sprints!$G$2:$G$210,0),1),

IFERROR(INDEX(Sprints!$A$2:$A$210,MATCH(D6,Sprints!$H$2:$H$210,0),1)&INDEX(":"&Sprints!$B$2:$B$210,MATCH(D6,Sprints!$H$2:$H$210,0),1),

IFERROR(INDEX(Sprints!$A$2:$A$210,MATCH(D6,Sprints!$I$2:$I$210,0),1)&INDEX(":"&Sprints!$B$2:$B$210,MATCH(D6,Sprints!$I$2:$I$210,0),1),"Not Started")))


Smartsheet Formula: My Formula References the the Correct Sheet and Ranges like Excel but yields a UPARSEABLE. I am wondering if I need the $ for the Sprint Range and if I have my AND misplaced

=IFERROR(INDEX({Sprints Range 1}, MATCH(ID1, {Sprints Range 2}, 0, 1), IF(AND(INDEX(":"{Sprints Range 3}, MATCH(ID1,{Sprints Range 2},0,1)))

Formula Conversion (2)

Excel Formula: This Formula is Searching the Scope Column for Key Words to Populate the Correct Role within the Role Column

=IF(ISNUMBER(SEARCH("As a Customer",I6)),"Customer",IF(ISNUMBER(SEARCH("As a SF Admin",I6)),"SF Admin",IF(ISNUMBER(SEARCH("As a CSR",I6)),"CSR",IF(ISNUMBER(SEARCH("As a Content author",I6)),"Content Author",IF(ISNUMBER(SEARCH("As a Marketing author",I6)),"Marketing Author",IF(ISNUMBER(SEARCH("As an Admin",I6)),"Admin",IF(ISNUMBER(SEARCH("As a business user",I6)),"Business User",IF(ISNUMBER(SEARCH("As an Appplicant",I6)),"Appplicant",IF(ISNUMBER(SEARCH("As a Drupal Admin",I6)),"Drupal Admin",IF(ISNUMBER(SEARCH("System needs",I6)),"System",IF(ISNUMBER(SEARCH("The system shall",I6)),"System",IF(ISNUMBER(SEARCH("As an Enterprise Member",I6)),"Enterprise Member",IF(ISNUMBER(SEARCH("As A Member",I6)),"Member",IF(ISNUMBER(SEARCH("As a User",I6)),"User",""))))))))))))))

Smartsheet Formula: I am working on Converting this one now and will post what i have shortly

Thanks for the help in Advance

Best Answers

«13

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!