Help with multi-conditional contains formula

jaymeb98 · 02/20/24

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:

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

Scenario 2:

If the Child Account column contains TN Oncology
and the Task Name column contains US
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 · 02/20/24

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 · 02/20/24

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

Hi @KPH

Thanks for responding! To answer your questions:

Looking to match the entire contents of the cell, not just a few letters
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 · 02/20/24

Hi @KPH,

Thanks for responding! To answer your questions:

Looking to match the entire contents of the cell, not just a few letters
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 · 02/20/24

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 · 02/20/24

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 · 02/20/24

Have you looked in to creating a Report instead?

jaymeb98 · 02/20/24

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 · 02/20/24

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

jaymeb98 · 02/20/24

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 · 02/20/24

Which one goes where though?

Help with multi-conditional contains formula

Answers

Help Article Resources

Categories