Lookup & Display First Non Empty Cell In A Range of cells within a column

Bradley Uhr

I am attempting to write a formula in a header cell that returns the first Non-Blank row in a series within the related column.  Excel permits this by using the Index and Match formulas but requires that "CONTROL, SHIFT & ENTER" be pressed for the formula to work.  

When attempting this in SmartSheet, I receive an "#INCORRECT ARGUMENT" error message.  Sigh!

Any help here is appreciated. 

Thank you.

Paul Newcome

You could use an INDEX/COUNTIFS. It looks as if you are using hierarchies, so I will leverage the CHILDREN function. If that's not correct, just change the ranges to whatever you need.

 

=INDEX(CHILDREN(), COUNTIFS(CHILDREN(), ISBLANK(@cell)))

 

Does this work?

Bradley Uhr

I tried the following without success in the Phase 2 Cell:

=INDEX(CHILDREN(Phase3:Phase8), COUNTIFS(Children(Phase3:Phase8), ISBLANK(@cell)))

Which "cell" are you referencing at the end of the formula?

The column is formatted to Text/Number

Thanks for your help.  This is above my pay grade. 

Paul Newcome

Try removing the ranges from the CHILDREN function. Leave those as CHILDREN(). When you don't specify a range in a CHILDREN function, it automatically looks at the children rows in the column that the formula is in.

 

The @cell reference just tells a function to look at each cell within the range on an individual basis.

Bradley Uhr

That worked.  Thank you.

Paul Newcome

Happy to help! 

Ross Novotny

@Paul Newcome Can this type of formula be used to find the first non-blank cell in a column? I am not getting the syntax right, but I am trying to find the contents of the first non-blank cell in a column.

Ross Novotny

Nevermind, I think I figured it out with this formula: =IFERROR(INDEX(COLLECT([Range]:[Range], [Range]:[Range], <>""), 1), "")