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

Options
Dan Tanner
Dan Tanner ✭✭✭✭
edited 06/06/22 in Formulas and Functions

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.

Answers

  • MVP OPS
    MVP OPS ✭✭✭✭✭
    Options

    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.


    best,

    Brad

    www.MVPOPS.com

  • Dan Tanner
    Dan Tanner ✭✭✭✭
    edited 06/06/22
    Options

    @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
    MVP OPS ✭✭✭✭✭
    Options

    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

    best,

    Brad

    www.MVPOPS.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!