Extract Predecessor Row Numbers

Hello, I'm trying to design a set of formulas to extract the individual predecessor row numbers from the Predecessor column. The idea is the first predecessor row number would appear in the P1 Row column, the second predecessor row number would appear in the P2 Row column, and so on and so forth (up to five predecessors). The goal is that after the predecessor row numbers are identified, then the Value columns could use a formula to match the Row number to the status of that row, and in turn determine a numerical value from the status (O = Not Started, 0.5 = In Progress, 1 = Complete). We will only be using Finish to Start predecessor logic so I think that extracting the values is possible. I'm just having a tough time figuring out all the components needed. Any help with these formulas would be much appreciated. I realize I probably need an auto numbered helper column to assist with this as well.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!