Simplify large if then statement.

TCJ

Hello

I have a large portfolio of projects. Each project can be in one 10 or so phases, and each phase can have 1 to 3 or 4 milestones during or at the end of the phase. I am trying to develop a sheet that will look at a project and the current phase, and confirm that the next milestone forecast makes sense. The challenge has been the multiple possible milestones for some phases. I have written a long complicated IF(And(OR formulas ) that works, but is difficult to change and modify if necessary. For several reasons, I would like a more straightforward means. Is there a better and more efficient way to perform this function. A Match/Index or similar Thank you.

Below is a sample to illustrate my process better

Project Name

Current Phase

Next Milestone

Milestone Sequence correct - this is where the IF (And (Or) formula goes

Phase - A list of the different phases of a project (this would not be on this sheet, but in drop downs on the actual project plan)

Milestones - A list of the possible milestones. As you can see, for example in Phase 3, there are 3 possible milestones.

(this would not be on this sheet, but in drop downs on the actual project plan)

Jgorsich

I'm not 100% clear on what you are trying to accomplish, but at a guess I think index(collect()) can get you were you want to go, especially with a match() tossed into the mix.

You can use collect() to grab all the phases and milestones for the project you are on - this will build a list of just the relevant phases and milestones. Use can then use match() on your current phase to figure out how far through that list you are. If you add 1 to the match and index to that value (basically, just tell it "figure out where we are and then go 1 further") you can return that value to get the next milestone.