Formula to Automatically Return Column Pending

Jennifer C

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.

Sample

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.

Paul Newcome

You can use a "helper row". You can use whichever row you want, but for this example I will use row 1.

In this helper row, you would enter the text you want displayed for each stage.

ColumnA..........ColumnB..........ColumnC..........ColumnD

Stage 1..............Stage 2..............Stage 3..............Stage 4

Then in the column you want to display the current stage in you would use a formula such as this:

=IFERROR(INDEX(ColumnA$1:ColumnD$1, 1, COUNTIFS(ColumnA@row:ColumnD@row, ISDATE(@cell)) + 1), "Complete")

Basically the INDEX function pulls the text from the top row.

We are only referencing a single row, so the row number for the INDEX function would be 1.

To determine the column, we count how many dates are entered on the current row and then add 1 to pull from the next one.

Finally we use the IFERROR to display "Complete" when there is an error since the only anticipated error would be when all dates are entered. 4 dates + 1 would be outputting a 5 for the column number in the INDEX function. Since you are only referencing 4 columns, then trying to pull from a 5th column will throw an error. Since the only time that happens is when all dates are filled in then we know it is "Complete" (or whatever text you want to output when all dates are filled in).