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

Chevon Brownell

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.

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.

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.

Chevon Brownell

That worked. Thank you!