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

06/11/19 Edited 12/09/19

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.

Screenshot 2019-06-11 11.20.50.png

Comments

  • Paul NewcomePaul 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?

    thinkspi.com

  • 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 NewcomePaul 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.

    thinkspi.com

  • That worked.  Thank you.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! yes

    thinkspi.com

  • Ross NovotnyRoss 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 NovotnyRoss Novotny ✭✭✭✭✭

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

Sign In or Register to comment.