Help for function to isolate last Modified By individual
Hello,
I have a table that has two columns with the auto-generated (last) Modified and (last) Modified By data. I have created a Summary Sheet field that spits out the most recent date of modification within the sheet. I am trying to do the same for the corresponding Modified By value, ultimately to have both of these pieces of information incorporated onto a Dashboard elsewhere.
What is the appropriate formula to return the value/username in the Modified By column that corresponds to the value generated by the =MAX(Modified:Modified) formula? In other words, how do I get the name of the individual (Modified By) who made the most recent edits (Modified Date)?
I'm trying an INDEX but it isn't working (pasting here what I've worked through so far, sorry for the errors): =INDEX(COLLECT((Modified By:Modified By), Modified:Modified, MAX(Modified:Modified)))
I appreciate any help.
Thanks,
Bryan
Answers
-
Hi Bryan,
You could use a JOIN(COLLECT formula for this, even though you don't have values to Join (you'll just be returning one cell's value, assuming that only one value meets all the criteria).
The way JOIN(COLLECT works is that you first list the range that has the value you want returned (your Modified By column in the source sheet), and then list each range and criteria afterwards. Try this:
=JOIN(COLLECT([Modified By]:[Modified By], Modified:Modified, MAX(Modified:Modified)))
You can read more about each of these functions in our Help Center: JOIN function / COLLECT function /Referencing Columns
Let me know if this works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P's solution should work for you. Here are some pointers on the INDEX/COLLECT for future reference...
It should work with a couple of slight adjustments.
You don't need parenthesis around the first range, and you would need to specify a row number (1 in this case) for the INDEX function.
Finally... When referencing a column name that has spaces, numbers, and/or special characters, you need to wrap the column name in square brackets (this is true for all functions/formulas).
=INDEX(COLLECT([Modified By]:[Modified By], Modified:Modified, MAX(Modified:Modified)), 1)
-
Ahh, thank you, @Paul Newcome ! I'm much more comfortable with JOIN(COLLECT so it's great to see INDEX(COLLECT spelled out 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P Sure thing! I can't remember the details, but I ran into an instance in the past where the INDEX function saved me a lot of effort as opposed to the JOIN function. I think it had to do with forms being entered at the top of the sheet and only needing the most recent.
Since the INDEX pulls the first one, I didn't have to go through and start messing with dates and times to flag the most recent for the JOIN.
At least I am pretty sure that was it. If it wasn't, at least it makes sense. Hahaha
-
Haha, that does make sense. Saving this post for future reference!
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!