Finding the oldest date on multiple sheets and also based on text in another cell

I have 2021, 2022, 2023, 2024, and 2025 sheets with different departments' requests for action (RFAs). I am looking for a formula to show me the oldest open RFA for each department. Some departments will be in 2021, but some will be in 2024. A sheet summary formula is not what I am looking for. I need a reference sheet with a formula that looks at all years and tells me the oldest, but I do not know how to set up the correct formula. Is there an easy way to do this? Here is an example of the 2024 sheet.

Find more posts tagged with

Formulas

Cell linking

References

IF(

summary report

Accepted answers

Adam Costello

I would use MIN(COLLECT to do this.

Make your reference sheet with your different departments in column 1 and then an oldest request column - where you'll put your formula.

The formula will look something like this:

=MIN(COLLECT({2021 Sheet Dates},{2021 Sheet Assigned to}, [Departments@row]), COLLECT({2022 Sheet Dates},{2022 Sheet Assigned to}, [Departments@row]))

You'll have to do a separate Collect formula for each sheet but it will collect all the dates that match the department and then spit out the earliest date.

All comments

Adam Costello

I would use MIN(COLLECT to do this.

Make your reference sheet with your different departments in column 1 and then an oldest request column - where you'll put your formula.

Chevon Brownell

That worked. Thank you!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.