Help for function to isolate last Modified By individual

stilesb

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

Genevieve P.

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

Paul Newcome

@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)

Genevieve P.

Ahh, thank you, @Paul Newcome ! I'm much more comfortable with JOIN(COLLECT so it's great to see INDEX(COLLECT spelled out 🙂

Paul Newcome

@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

Genevieve P.

Haha, that does make sense. Saving this post for future reference!