Using the IF and OR Functions in Combination
Hello,
I am stuck trying to figure out how to use IF and OR in combination.
I am using Smartsheet to track and monitor a project. I have an overview cell, Cell A, that I would like to show what overall phase my project is in, based on the statuses of the different phases in my project sheet.
More specifically,
I would like Cell A to show what phase the project is in, based on status of the different phases
Column 1: Phase, Column 2: Status
Phase 1, Completed
Phase 2, Completed
Phase 3, In Progress
Phase 4, Not Started
Cell A/Project Phase would read Phase 3 to indicate that the project is in Phase 3, Phase 3 is In Progress.
So, I think I need something along the lines of
IF(Phase 1 = In Progress, "Phase 1"), OR(Phase 2 = In Progress, "Phase 2"), OR(Phase 3 = In Progress, "Phase 3"), OR(Phase 4 = In Progress, "Phase 4")
Can somebody please help me? Thank you!
Best Answers
-
Try this:
=INDEX(CHILDREN([Milestone/Task]@row), MATCH("In progress", CHILDREN(Status@row), 0))
-
Try indenting what you currently show under the blue line so that the white lines are children of the blue line.
If you prefer not to indent, you can change each of the ranges from
CHILDREN([Column Name]@row)
to
[Column Name]:[Column Name]
Answers
-
Are you able to provide screenshots for context?
-
Thank you @Paul Newcome This is a screenshot of the project sheet. I am looking to populate the cell, "Onboarding Phase" with the Milestone that is currently In progress. Thanks for your help!
-
Try this:
=INDEX(CHILDREN([Milestone/Task]@row), MATCH("In progress", CHILDREN(Status@row), 0))
-
@Paul Newcome No, that did not give me the results I was looking for unfortunately. It looks like that formula relies on the child row of the main task, and so it returns the incorrect row's task.
What I'm looking for is that when the main/master row (1st column- "Phase") has a status of "In progress" (2nd column- "Status"), the sub header cell (outlined in red in attached screenshot) shows the Phase corresponding to the "In progress" status.
So in my screenshot, because the phase, "Testing & Rollout has a status of In progress, under Onboarding Phase, it shows Testing & Rollout.
-
Try indenting what you currently show under the blue line so that the white lines are children of the blue line.
If you prefer not to indent, you can change each of the ranges from
CHILDREN([Column Name]@row)
to
[Column Name]:[Column Name]
-
That definitely did the trick! Thank you Paul!
FYI I also got another formula to work for me- =VLOOKUP("In progress", Status2:[Milestone/Task]24, 2, false). This only worked if I switched the order of my columns though, as this formula relies on the returned result being in the first column only.
That said, I like your solution better- thanks again!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 352 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 135 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!