Find first non-empty cell

Hi,


What formula can I use in the Current Email Helper column to accomplish the below Desired Outcome. I'm not using a hierarchy so I cant use CHILDREN()/PARENT() functions.

Here's the kind of logic I'm trying to follow:

  • IF [Email]@row is empty
  • THEN Look for the first cell above [Email]@row that has an email and return that value
  • ELSE return Email@row

Current

Desired Outcome


Thanks 😀

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    edited 09/17/21

    Oh you're not using hierarchy, I wrote a response with hierarchy so never mind that.

    If you're OK with this not being a column formula this would go in row 2 and drag down.

    =IF(ISBLANK(Email@row), [Email Helper]1, Email@row)

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!

    PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!

  • Hi Dan,

    Thanks for your response.

    I also came up with this solution at some stage. However, I can't use it in this instance as I require a dynamic formula. The rows archive after 30 days (sent to another sheet). New rows are also added by way of form submissions (the two form solution proposed by @Andrée Starå). The two put together will definitely cause me issues without a dynamic formula.

    If there was only a way to reference the row above the current row in Smartsheet, life would be so much easier:

    [Email Helper]@(row-1)

    @Andrée StaråThanks

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Insert an auto-number column (called "Auto") with no special formatting or anything. Then insert a text/number column (called "Row") with the following column formula:

    =MATCH(Auto@row, Auto:Auto, 0)


    Now you should be able to use...

    =IFERROR(IF(Email@row <> "", Email@row, INDEX(Email:Email, Row@row - 1)), "")

  • Hi @Paul Newcome ,

    Thanks for the response.

    I really like the row number solution, it'll definitely come in handy.

    However, the solution won't work in my case as the entire Email column is populated via form submission (can't have formulas in it).

    I tried the following which would work in theory, but I get a #CIRCULAR REFERENCE error:

    =IFERROR(IF(Email@row <> "", Email@row, INDEX([Email Helper]:[Email Helper], Row@row - 1)), "")


    I'm using the below temporary solution which utilizes a helper row. The only problem is that when the sheet gets archived and rows are moved to another sheet, I get a #REF error.

    [Email Helper]1 = Email@row

    [Email Helper]2 =IF(Email@row <> "", Email@row, INDEX([Email Helper]$1:[Email Helper]1, COUNTIF([Email Helper]$1:[Email Helper]1, NOT(ISBLANK(@cell))) + COUNTIF([Email Helper]$1:[Email Helper]1, ISBLANK(@cell))))

    Row#2 is then dragged down to fill the rest of the column.


    Maybe you have a workaround?

    Thanks ☺️

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Heval A.

    You're more than welcome!

    FYI. I have an idea for a solution, but I haven't had time to explore it further. I'll get back to you if it works.

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The formula I provided was supposed to go into the [Email Helper] column. The reason for the circular refence is because you are referencing the column that the formula is in. The Email column being populated by a form is not an issue. The real issue is that putting my formula in the [Email Helper] column would output a blank on (for example) row 3 because Email2 is blank. Try this instead.

    =INDEX(Email:Email, MAX(COLLECT(Row:Row, Row:Row, @cell<= Row@row, Email:Email, @cell <> "")))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!