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

Find more posts tagged with

Formulas

Project Management

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.