nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

Find MAX non-blank cell with matching criteria

mcancian

Hi all!

I'm working on a column formula that will check whether the Column2ref cell in the current row is blank. If it is blank, the formula finds the maximum value in the ID column where the Trainer Name matches the Trainer Name cell value in the current row and the ID cell value is less than the ID cell value in the current row, then returns the corresponding Column2ref cell value for that maximum ID. If the Column2ref cell in the current row is not blank, the formula returns the Column2ref cell value in the current row.

My current formula is executing this perfectly, but I need to adapt it so that if the matched Column2ref cell is empty, the formula will continue searching for the next highest value in the ID column (while still matching the Trainer Name). It should keep searching until it finds the highest ID column cell value where the Trainer Name matches the Trainer Name in the current row and the Column2ref cell is not empty, then it should pull that identified Column2ref value into the formula column in the current row.

This is my current formula: =IF(ISBLANK([Column2ref]@row), INDEX(COLLECT([Column2ref]:[Column2ref], [Trainer Name]:[Trainer Name], [Trainer Name]@row, ID:ID, MAX(COLLECT(ID:ID, [Trainer Name]:[Trainer Name], [Trainer Name]@row, ID:ID, <ID@row))), 1), [Column2ref]@row)

And here is a screenshot of the sheet I am working with:

Thanks so much!

Find more posts tagged with

Formulas

Accepted answers

All comments

There are no accepted answers yet

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, category/bannerUrl, category/bannerImageAlt, category/extensions, category/customizationLabels, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/bannerImageAlt, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, latestCommentPosition, serverDraftID, serverDraft.