Formula to Automatically Return Column Pending

I have a Smartsheet that is being used to login dates as an item is processed through various steps until completion. I would like to create a column that automatically shows what step in the process the item is on. I can do this by combining if/then formulas, but there are at least 15 steps and I am hoping there might be a better way to create the formula.


Column A Column B Column C Column D

Shows a Date Shows a Date No Date Showing No Date Showing

I want the formula to automatically tell me that Column C is where the item is in the process. Then, when a date is added to column to column C, it will show column D.


Best Answer


  • Gil Nash
    Gil Nash ✭✭✭

    If you're just looking for something visually to ease you in quickly discerning what Step you are in, conditional formatting might be an easier way to accomplish this. Set all Date cells that are blank to turn yellow.

    If nobody chimes in, I'll work on a solution later when I have time.

  • Jennifer C
    Jennifer C ✭✭✭

    Thank you Gil and Paul! The Index formula worked like a charm. I already have conditional formatting being used to tell what department it item is in since it bounces back and forth between 3 departments multiple times, but I wanted something to say, it is in X department by color and the the status column to say what process it was at without have to scroll through a bunch of columns to see the last date.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!