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.
Best Answer
-
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.
Answers
-
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. -
That worked. Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.1K Get Help
- 450 Global Discussions
- 155 Industry Talk
- 505 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 156 Just for fun
- 80 Community Job Board
- 514 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!