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

Jeana
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

Best Answers

Answers

  • Jeana
    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!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 08/10/20

    @lewis hamilton

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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Jeana
    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
    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.
    Genevieve P. Employee Admin

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!