Return first NOT BLANK field in a column and first BLANK field in a column

Jeana

Hi,

I have a column with a formula that calculates the Current Step Calc in a process based on there being a date or not in a Task Started column. That part is working fine.

My results look like this in the Next Step Calc and Current Step Calc columns

What I'm struggling with is a formula in the NEXT STEP column that will return the FIRST cell with data in it from the Next Step Calc and another formula that will return the LAST cell with text in it from the Current Step Calc.

I've tried a few formulas including this one:

=IF([Next Step Calc]:[Next Step Calc] <> " ", [Next Step Calc]@row, "Not true")

The error says BLOCKED so I'm assuming that the formula can't reference a cell in the range? Not sure. Any advice?

Thanks,

Jeana

Genevieve P.

Hi Jeana,

I've thought of one way to do this, but it would require one more helper column... this column would indicate the top row of your Next Step column and the last row with content in your Current Step column.

The first part would check the cell above to see if it's empty, and if it is, return "Next". Let's pretend that this is in ROW 10.

=IF(AND([Next Step Calc]@row <> "", [Next Step Calc]9 = ""), "Next", ""

Notice that the formula is looking to see if the row above (row 9) is blank. If it is, then this current row is the next step.

Now, if it's not the Next step, it could be the Current step... so we'll add that IF statement into the same formula... again pretending this is in ROW 10:

IF(AND([Current Step Calc]@row <> "", [Current Step Calc]11 = ""), "Current", ""))

You'll see that this is looking into row 11, or the row below to see if it's empty or not.

Here's the full helper formula:

=IF(AND([Next Step Calc]@row <> "", [Next Step Calc]9 = ""), "Next", IF(AND([Current Step Calc]@row <> "", [Current Step Calc]11 = ""), "Current", ""))

Otherwise, it will leave the cell blank. Now from here you could do two things... you could use this result to then colour the row with Conditional Formatting. Or, if you need to pull the actual name of that row, you can use this indicator in an INDEX(MATCH formula:

Current Step

=INDEX([Current Step Calc]:[Current Step Calc], MATCH("Current", [Next/Current]:[Next/Current]))

Next Step

=INDEX([Next Step Calc]:[Next Step Calc], MATCH("Next", [Next/Current]:[Next/Current]))

Here's how I set it up. The Yellow Cells are where I have the formulas above.

Here are some Help Center articles I used to help build this:

• IF function
• AND function
• INDEX function
• MATCH function

Let me know if this makes sense or if you have any questions!

Cheers,

Genevieve

Genevieve P.

Hi Jeana,

I'm glad that this works for you! (For the most part, haha).

In regards to the BLOCKED error, this could happen if there's an error in either of the two columns it's looking into. Can you scroll all the way down each column and look to see if there are any other formula errors in either the Next Step Value column or the Next/Current step Calc column?

Genevieve P.

Hi Jeana,

I've thought of one way to do this, but it would require one more helper column... this column would indicate the top row of your Next Step column and the last row with content in your Current Step column.

The first part would check the cell above to see if it's empty, and if it is, return "Next". Let's pretend that this is in ROW 10.

=IF(AND([Next Step Calc]@row <> "", [Next Step Calc]9 = ""), "Next", ""

Notice that the formula is looking to see if the row above (row 9) is blank. If it is, then this current row is the next step.

Now, if it's not the Next step, it could be the Current step... so we'll add that IF statement into the same formula... again pretending this is in ROW 10:

IF(AND([Current Step Calc]@row <> "", [Current Step Calc]11 = ""), "Current", ""))

You'll see that this is looking into row 11, or the row below to see if it's empty or not.

Here's the full helper formula:

=IF(AND([Next Step Calc]@row <> "", [Next Step Calc]9 = ""), "Next", IF(AND([Current Step Calc]@row <> "", [Current Step Calc]11 = ""), "Current", ""))

Otherwise, it will leave the cell blank. Now from here you could do two things... you could use this result to then colour the row with Conditional Formatting. Or, if you need to pull the actual name of that row, you can use this indicator in an INDEX(MATCH formula:

Current Step

=INDEX([Current Step Calc]:[Current Step Calc], MATCH("Current", [Next/Current]:[Next/Current]))

Next Step

=INDEX([Next Step Calc]:[Next Step Calc], MATCH("Next", [Next/Current]:[Next/Current]))

Here's how I set it up. The Yellow Cells are where I have the formulas above.

Here are some Help Center articles I used to help build this:

• IF function
• AND function
• INDEX function
• MATCH function

Let me know if this makes sense or if you have any questions!

Cheers,

Genevieve

Jeana

HI Genevieve!!!

That solution is genius!!! To have the formula look at the cells above and below in the column worked great, THANK YOU! The only issue I still have if the formula to INDEX/MATCH and pull in the actual NEXT step. It works fine for the Current step but not on the next step. I get a #BLOCKED error. I reworded a few columns to help myself better understand so here's the updated pic of what I'm getting.

Thanks!!!

I'm using your exact formulas minus the column name change.

Thanks!

Genevieve P.

Hi Jeana,

I'm glad that this works for you! (For the most part, haha).

In regards to the BLOCKED error, this could happen if there's an error in either of the two columns it's looking into. Can you scroll all the way down each column and look to see if there are any other formula errors in either the Next Step Value column or the Next/Current step Calc column?

Andrée Starå

@lewis hamilton

Your solution isn't for Smartsheet, so it, unfortunately, won't work.

Jeana

Genevieve,

I found the error in one of the columns that was causing the #BLOCKED error. All fixed now. The last issue I have is that the formula is only picking up results from the first set of CHILDREN. So in this pick the first CURRENT calculation is correct. The second Parent is displaying the results from the first one, not the CURRENT parent. I realize this is because we are evaluating the entire column and that's the first time that matches the conditions.

How do I update the formula to re-evaluate for each Parent's set of Children?

Thanks!!!

Jeana

Jeana

Well I figured out one way to do it. Added another column with this formula. A lot of columns but it now works!!!

Thanks so much for your input and if you have a better idea I'm listening!

Jeana

Genevieve P.

Hi Jeana,

That's a lot of helper columns... but it looks like it works for you! As long as you know what each column is doing then I think that's find. Just remember to lock them if you don't want anyone else to touch the formulas, and potentially hide as many as possible so you just see the end result.

Glad you got it working in the end!