Want to have contact list automated by "last modified".

Dan Tanner

Good afternoon everyone,

Seems like a simple formula, but I'm having some issues figuring it out. Essentially, I want to have a "contact list" column automated by a "last modified by" column.

Example:

If Dan Tanner modifies a row, the "last modified" would be "dantanner@email.com" and that would trigger the "contact" column be "Dan Tanner".

Seems a little strange, I know, but I want both columns on an input sheet to be hidden but need them to feed and be seen in reports and dashboards.

MVP OPS

Hi @Dan Tanner

For this you would add a system generated column "Modified by" and whomever modified last will be tagged. Then you could either create a formula or automation to change the user in your contact column based on the email in the modified column. Hope this helps.

Dan Tanner

@MVP OPS Thank you. I already have a "Modified By" system generated column and "Contact List" column. Unfortunately, we can't create an automation that changes a "contact list" column (or at least that I can see) so I'm looking for a formula that can assist.

MVP OPS

Hi @Dan Tanner,

Ok, not super straight forward but how I would build this:

Create a separate sheet that will store your users

Add a contact column - add all of your users to the contact dropdown

Add a text/number column - input the email that will appear in your "Modified by"

Once you have this sheet created. In your main sheet in your contact column create an index(Match formula that indexes your contact column in new contact sheet that matches your Modified By column, to the email in your new sheet. Something like this.

=INDEX({contacts Range 1}, MATCH([Modified By]@row, {contacts Range 2}, 0))

Where:

{{contacts Range 1}} = New sheet Contact Column

[Modified By]@row= Modified row in main sheet

{contacts Range 2}= New sheet email column

Hope this helps