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

I understand what you are trying to do. Unfortunately Smartsheet doesn't quite function the same way as Excel, so we need to approach it a little differently. You need to have some data on the Main sheet that is unique to each row that is also on the Reference sheet that is unique to each row there. Basically the way it will have to look is: ="Sprint " + INDEX({Reference Sheet Sprint # Column}, MATCH([Unique Data Column]@row, {Reference Sheet Unique Data Column}, 0)) + ": " + INDEX({Reference Sheet ACR Column}, MATCH([Unique Data Column]@row, {Reference Sheet Unique Data Column}, 0))

Yes. You would wrap it in an IFERROR statement. =IFERROR(original formula, "Not Started")

Formula Referencing from Another Sheet

Answers

Anthony Barthelemy ✭✭✭✭✭✭

03/09/20

Thanks

0 · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

Can you help me with a COUNTIFS Formula (Second Criteria)
Trying to count Rows in a sheet that meet 2 criteria. I am not an advanced user, so I am missing something. Can you take a look at this formula and give me some suggestions? =COUNTIFS({Phase-TPN}, "1", [Start SCRA]:[Start SCRA], "Yes")
Can you convert this vlookup to an index match or index collect?
basically, it rest id exists in both SS and on the other SS a field is either checked or unchecked, then result should read Yes. =IFERROR(IF(AND(VLOOKUP([Restaurant ID]@row, {CFA NRO/Remodel Cabling Tracking Range 1}, 1, false) <> 0, VLOOKUP([Restaurant ID]@row, {CFA NRO/Remodel Cabling Tracking Range 1}, 39, false), 0),…
Help with Health Formula
When I put this formula: =IF([% Complete]@row = "100%", "Green", IF(AND([% Complete]@row <> "100%", [Due Date]@row >= TODAY()), "Yellow", IF(AND([% Complete]@row <> "100%", [Due Date]@row < TODAY()), "Red", ""))) The status shows Red when the % complete is 100% and the Due date is past. How can I correct that? Thanks

Formula Referencing from Another Sheet

Answers

Help Article Resources

Categories