Referencing another Column in the same Row after finding the MIN with Criteria

I am trying to find the next upcoming Date and its associated Task based off what activity is being performed and where it's taking place.

The formula I have to find the next upcoming date works perfectly:

=MIN(COLLECT({Date}, {Date}, >=TODAY(), {Activity}, ="Activity ", {Area}, ="Area"))

My problem is trying to get the Task associated with that date. (Pull information from another column in that same row)

I was attempting to use an INDEX/MATCH but what happens is that it goes through the Date column and pulls the first instance where the dates match, which is not the correct row.
=INDEX({Task}, MATCH(MIN(COLLECT({Date}, {Date}, >=TODAY(), {Activity}, ="Mechanically Complete", {Area}, ="CUB", {Children}, =0)), {Start}, 0))

Is there a way of getting the correct date and then pulling its associated Row # so that INDEX/MATCH would correctly find the Task?

Find more posts tagged with

Accepted answers

Paul Newcome

You would use this in a date type column (called "Helper" in this example):

=MIN(COLLECT({Date}, {Date}, >=TODAY(), {Activity}, ="Mechanically Complete", {Area}, ="CUB", {Children}, =0))

Then this to pull in what you are looking for:

=INDEX(COLLECT({Task}, {Date}, @cell = Helper@row, {Activity}, @cell = "Mechanically Complete", {Area}, @cell = "CUB", {Children}, @cell = 0), 1)

All comments

Paul Newcome

Try an INDEX/COLLECT.

=INDEX(COLLECT({Task}, {Date}, @cell >= TODAY(), {Activity}, @cell = "Mechanically Complete", {Area}, @cell = "CUB", {Children}, @cell = 0), 1)

Arius

Paul,

Thanks for the reply! This formula is partly doing what I want. It's able to output the correct information/column; however, this formula only gives me the first occurrence that meets the criteria. If a row lower in the column has a date closer to today, it is ignored.

Paul Newcome

@Arius Not sure I follow. The MIN function should be going to the one closest to today that is either equal to today or in the future. Are you able to provide some screenshots of what it is outputting vs what it should be?

Arius

@Paul Newcome,

The formula you gave did not contain a MIN function, so it just picks the first occurrence that meets the conditions.

I want the result to be 'Row that has the date closest to today that meets all Criteria'. If the date for that row changes to something later, then it would output 'Activity 3'.

I hope this makes more sense.

Paul Newcome

You're right. My apologies.

You will need to use the MIN/COLLECT to output the date in a separate column on the target sheet, then reference that helper column in the INDEX/COLLECT.

Arius

I'm not entirely sure what you're suggesting.

Using my MIN/COLLECT as a helper column only has a result output to one cell. Not sure how I could use that one cell as a reference to find the correct row.

Smartsheet wont allow your formula as a helper column because there are too many cell references.

Paul Newcome

You would use this in a date type column (called "Helper" in this example):

=MIN(COLLECT({Date}, {Date}, >=TODAY(), {Activity}, ="Mechanically Complete", {Area}, ="CUB", {Children}, =0))

Then this to pull in what you are looking for:

=INDEX(COLLECT({Task}, {Date}, @cell = Helper@row, {Activity}, @cell = "Mechanically Complete", {Area}, @cell = "CUB", {Children}, @cell = 0), 1)

Arius

~~Placing that Helper column in the Main Sheet doesn't work; Smartsheet says there are too many cells referenced by formulas.~~

~~Making that helper column in my Analysis Sheet and then using the second formula gives me the same incorrect task shown in my previous screenshots.;~~

I apologize, upon further looking at the formula, I seem to be getting the correct results! Thank you, Solved!

Paul Newcome

Happy to help. 👍️

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, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.