Lookup & Display First Non Empty Cell In A Range of cells within a column
I am attempting to write a formula in a header cell that returns the first NonBlank 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.
Comments

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?

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.

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.

That worked. Thank you.


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

Nevermind, I think I figured it out with this formula: =IFERROR(INDEX(COLLECT([Range]:[Range], [Range]:[Range], <>""), 1), "")
Help Article Resources
Categories
Check out the Formula Handbook template!