Help with multi-conditional contains formula
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 :)
Answers
-
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?
-
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.
-
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.
-
Why not just provide view only permissions to the person or people who you are wanting to build this second view only sheet for?
-
@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
-
Have you looked in to creating a Report instead?
-
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
-
How would you want these outputs displayed? Are you able to provide a screenshot of a mocked up sample of the expected outcome?
-
Wanting it to look like this where the status value from the other sheet would be inputted into the yellow highlight:
-
Which one goes where though?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!