Find first non-empty cell

09/16/21
Answered - Pending Review

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 protected]

Current

Desired Outcome


Thanks 😀

Answers

  • SoS | Dan PalencharSoS | 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 protected]), [Email Helper]1, [email protected])

  • 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 NewcomePaul 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([email protected], Auto:Auto, 0)


    Now you should be able to use...

    =IFERROR(IF([email protected] <> "", [email protected], INDEX(Email:Email, [email protected] - 1)), "")

    thinkspi.com

  • 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 protected] <> "", [email protected], INDEX([Email Helper]:[Email Helper], [email protected] - 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 protected]

    [Email Helper]2 =IF([email protected] <> "", [email protected], 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 PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul 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<= [email protected], Email:Email, @cell <> "")))

    thinkspi.com

Sign In or Register to comment.