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

  • Adam Costello
    Adam Costello ✭✭✭✭
    edited 02/14/25 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

  • Adam Costello
    Adam Costello ✭✭✭✭
    edited 02/14/25 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.

  • That worked. Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!