Looking for the most recent date - across several sheets

Josephine

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!

Josephine

I found another article for the same thing -

https://community.smartsheet.com/discussion/93263/max-collect-from-multiple-sheets

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

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

Josephine

Yes I did :)

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

Paul Newcome

Lets try this...

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

Josephine

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

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?

Josephine

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!