Using the IF and OR Functions in Combination

Jeremy Weigens

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!

Paul Newcome

Try this:

=INDEX(CHILDREN([Milestone/Task]@row), MATCH("In progress", CHILDREN(Status@row), 0))

Paul Newcome

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]

Paul Newcome

Are you able to provide screenshots for context?

Jeremy Weigens

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!

Paul Newcome

Try this:

=INDEX(CHILDREN([Milestone/Task]@row), MATCH("In progress", CHILDREN(Status@row), 0))

Jeremy Weigens

@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.

Paul Newcome

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]

Jeremy Weigens

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!

Paul Newcome

Happy to help. 👍️