Looking for the most recent date - across several sheets

Hi all,

This formula works perfectly when referring to 1 sheet -

=MAX(COLLECT({Competencies Awarded Range 1}, {Competencies Awarded Range 2}, "Bob Jones"))

The forumla is to find the latest date that a internal learning unit was submitted.

However due to the number of internal units it has been decided to split the sheet up into several sheets. So now I need to find the last date a unit was submitted across several sheets. is this possible?

Clumsy work around would be to create a collation sheet (use the above formula for each sheet into 1 sheet) and then refer to that but ideally the formula would be smart enough!

Answers

  • I found another article for the same thing -

    and am trying =MAX(MAX(COLLECT(.....)), MAX(COLLECT(.....)), MAX(COLLECT(.....))) but am getting "invalid data type". All columns are date columns.

    This is the latest attempt..

    =MAX(MAX(COLLECT({Date awarded}, {Name}, "Bob Jones")), MAX(COLLECT({Date Awarded - ESG}, {Name - ESG}, "Bob Jones")), MAX(COLLECT({Date - HSE}, {Name - HSE Range 1}, "Bob Jones")))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Have you made sure the column you are actually putting the formula into is also set as a date type column?

  • Yes I did :)


    I was working but then the source sheet was split into several sheets

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Lets try this...


    Temporarily put each of the individual MAX/COLLECTs in there own cells. What do we get?

  • Hi Paul,


    Thanks for trying to trouble shoot with me.

    Initially the ESG sheet seemed to be the issue I ended up making a copy of that sheet and deleting the original. I created a collation sheet and I can get the formula to work in the collation sheet with only doing 1 max collect per column but trying to do the full formula in my other sheet and it doesnt work, even though each individual sheet is fine. I also tried to do the formula referencing 2 of the other sheets in this sheet which is the Collation Column (not the columns in this sheet)

    Within this sheet I can get the formula to work if I just reference 2 columns, as soon as I reference 3 it stops working

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 04/13/23

    What is the third one you are referencing that breaks it?


    Did you try separating each of the MAX/COLLECTS out into their own cells to see if it is always the same one having an issue?

  • Hi @Paul Newcome,

    I managed to solve this in the end by creating a column in my main sheet for each of the other sheets I wanted to collect dates from, I did a MAX(COLLECT formula for each column, I then did a MAX across the columns and have hidden the columns from view.

    Bit more clumsy than I had been hoping but it works!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!