Formula Referencing from Another Sheet

Anthony Barthelemy

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

Paul Newcome

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))

Paul Newcome

Yes. You would wrap it in an IFERROR statement.

=IFERROR(original formula, "Not Started")

Anthony Barthelemy

Formula (2) Smartsheet Formula: Here is what I started with

=IF(ISTEXT([Scope Item]:[Scope Item], "As a Customer", "Customer"))

I want the formula to search the Scope Item Column for "As a Customer" and if found populate the Customer in the Role Column.

Anthony Barthelemy

Smartsheet Community I have the first part of Formula 2 Correct. Now the challenge is adding the other conditions to the formula to search the text for SF Admin and the other roles

=IF(ISTEXT([Scope Item]@row), "As a Customer", "Customer")

I should be able to just add to this Formula and get SF Admin if it sees it in the Scope Item, correct?

=IF(ISTEXT([Scope Item]@row), "As a Customer", "Customer", IF(ISTEXT([Scope Item]@row), "As a SF Admin", "SF Admin"))

Anthony Barthelemy

Smartsheet Community I was able to get formula 2 Working Successfully:

=IF(CONTAINS("As a Customer", [Scope Item]@row), "Customer", IF(CONTAINS("As a SF Admin", [Scope Item]@row), "SF Admin", IF(CONTAINS("As a CSR", [Scope Item]@row), "CSR", IF(CONTAINS("As a Content Author", [Scope Item]@row), "Content Author", IF(CONTAINS("As a Marketing Author", [Scope Item]@row), "Marketing Author", IF(CONTAINS("As a Admin", [Scope Item]@row), "Admin", IF(CONTAINS("As a Business User", [Scope Item]@row), "Business User", IF(CONTAINS("As an Applicant", [Scope Item]@row), "Applicant", IF(CONTAINS("As a Drupal Admin", [Scope Item]@row), "Drupal Admin", IF(CONTAINS("System Needs", [Scope Item]@row), "System", IF(CONTAINS("The System Shall", [Scope Item]@row), "System", IF(CONTAINS("As a Enterprise Member", [Scope Item]@row), "Enterprise Member", IF(CONTAINS("As a Member", [Scope Item]@row), "Member", IF(CONTAINS("As a User", [Scope Item]@row), "User"))))))))))))))

Now I have to get Formula 1 Working which reference another sheet. Therefore, any and all help is appreciated.

Thanks

Anthony Barthelemy

@Paul Newcome can you help me with the first formula? I can share the sheets with.

Paul Newcome

I notice a couple of things right off with this formula:

=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)))

The first match has one too many fields in it, and I am unsure of the purpose of both the second IF and the AND statement. What is the intended outcome of the formula as a whole?

Anthony Barthelemy

To Assign the correct Sprint Task Name based on the Sprint#, Req Id, and the ID Number, otherwise the Sprint Task should display Not Started.

I can share the source excel sheet if you like as an attachment to Smartsheet?

Paul Newcome

Ok. So an INDEX/MATCH can only be used to match on one field. Try using a JOIN/COLLECT instead.

=JOIN(COLLECT({Other Sheet Task Name Column}, {Other Sheet Sprint# Column}, [Sprint#]@row, {Other Sheet Req ID Column}, [Req ID]@row, {Other Sheet ID Number Column}, [ID Number]@row))

Does that work for a start?

Are you able to provide a screenshot of the two sheets so I can see the layout of them including column names?

Anthony Barthelemy

I tried it and received circular reference:

=JOIN(COLLECT({Sprints Range 1}, {Sprints Range 2}, [Sprint Task]@row, {Sprints Range 2}, [Sprint Task]@row, {Sprints Range 3}, ID@row))

Paul Newcome

Can you provide a screenshot of both the source and target sheets to include the column names? What column are you putting the formula in?

Anthony Barthelemy

I will just share it with you. Whats your email address?

Paul Newcome

Are you able to put them both in a folder then Save the Folder as New to create a set that can be edited without fear of losing data if some mistake is made? You can then publish both and provide those links.

Anthony Barthelemy

Yes, I already created copies, attached the excel sheet (source) to the attachment in in Smartsheet, and provide the link to the Reference Smartsheet.

Ready to share with you

Paul Newcome

Can you post the published links here?

Anthony Barthelemy

Main Smartsheet:

https://app.smartsheet.com/b/publish?EQBCT=dbc2923e7e17492aa10dde66cb9e682c

Reference Smartsheet Sheet:

https://app.smartsheet.com/b/publish?EQBCT=04667dc538b64aa7ad358f41347c6183

Paul Newcome

What is covered by your ranges?

{Sprints Range 1}

{Sprints Range 2}

{Sprints Range 3}