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")))
-
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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!