# Formula Referencing from Another Sheet

Options
✭✭✭✭✭✭

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

«13

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
edited 03/03/20
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
edited 03/04/20
Options

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

• ✭✭✭✭✭✭
Options

Can you post the published links here?

• ✭✭✭✭✭✭
Options

What is covered by your ranges?

{Sprints Range 1}

{Sprints Range 2}

{Sprints Range 3}

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!