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

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

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

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.

Paul Newcome

Happy to help. 👍️