Find first non-empty cell

Heval A.

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 😀

SoS | Dan Palenchar

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)

Heval A.

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

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)), "")

Heval A.

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å

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

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