Help with multi-conditional contains formula

jaymeb98

Hi there! I am struggling with finding a formula to assist with the following two scenarios. Please see screenshot for layout of columns for reference

Scenario 1:

1. If the Child Account column contains TN Oncology
2. and the Task Name column contains CDA Signed
3. I want the value that is populated in the Status column to pull into a new sheet

Scenario 2:

1. If the Child Account column contains TN Oncology
2. and the Task Name column contains US
3. I want US to pull into a new sheet

Any assistance would be greatly appreciated, I am losing my mind over something I'm sure is a simple fix :)

KPH

Hi @jaymeb98

I think an INDEX COLLECT formula will do what you need and can help put one together for you if I may I ask two clarifying questions first.

1

When you say "contains" do you mean contains as in the smartsheet function CONTAINS? This function looks at the string within the cell and returns true or false based on whether the cell has the lookup value anywhere within it. So both of these cells CONTAIN "US"

If not, and you only need to match the entire contents of the cell, we can use equals.

2

Where in the second sheet do you want this information to appear?

jaymeb98

https://community.smartsheet.com/discussion/comment/420738#Comment_420738

Hi @KPH

Thanks for responding! To answer your questions:

1. Looking to match the entire contents of the cell, not just a few letters
2. There are blank columns that align with this information that I'm trying to build into a 'view' only sheet so that the data pulls from this sheet into that one.

jaymeb98

Hi @KPH,

Thanks for responding! To answer your questions:

1. Looking to match the entire contents of the cell, not just a few letters
2. There are blank columns that align with this information that I'm trying to build into a 'view' only sheet so that the data pulls from this sheet into that one.

Paul Newcome

Why not just provide view only permissions to the person or people who you are wanting to build this second view only sheet for?

jaymeb98

https://community.smartsheet.com/discussion/comment/420812#Comment_420812

@Paul Newcome - they are wanting the view only sheet to be in a higher-level display. The source sheet has many more details whereas the view only document is mainly status pulls

Paul Newcome

Have you looked in to creating a Report instead?

jaymeb98

https://community.smartsheet.com/discussion/comment/420883#Comment_420883

Yes, it made it even more challenging because the two sheets are in very different layouts. That is why I'm needing help with a formula to pull the data into the high level sheet

Paul Newcome

How would you want these outputs displayed? Are you able to provide a screenshot of a mocked up sample of the expected outcome?

jaymeb98

https://community.smartsheet.com/discussion/comment/420887#Comment_420887

Wanting it to look like this where the status value from the other sheet would be inputted into the yellow highlight:

Paul Newcome

Which one goes where though?