Formula: Which User Last Modified: Column 1 row 2

Options
A Rose
A Rose ✭✭✭✭
edited 01/07/21 in Formulas and Functions

Hi,

Is there a way to show who modified a cell, using a formula?

I don't wan't to know who modified the row, i want to know in column 1 row 2, which user last modified that cell..


Thank you!

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    There is no direct way, but there is a workaround that I think might work. It involves a few helper columns, an automation, and some cross sheet formulas.


    Insert a system generated Modified by column.

    Insert an auto-number column. You don't have to worry about setting anything special for this one.

    Insert a text/number column and use this column formula to replicate the row number in a manner that can be used by other formulas:

    =MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)

    Next we add two more text/number columns that we will revisit in a moment.


    Now we duplicate your sheet and remove all data from the duplicate.

    Add a system generated Created (date) column to this one.


    Now we go back to the original sheet to finish out the solution...

    Setup a Copy Row Automation that is to be triggered whenever [Column 1] changes and have the copied row sent to the duplicate sheet.

    Change a few rows to go ahead and test that the automation is working and to get some data onto the duplicate sheet.

    Now we go back to one of those text/number columns that we left blank and enter...

    =MAX(COLLECT({Duplicate Sheet Created (date) Column}, {Duplicate Sheet Row Number Column}, [Row Number]@row)) + ""

    Finally we go to that last text/number column we had created and enter...

    =INDEX(COLLECT({Duplicate Sheet Modified by Column}, {Duplicate Sheet Row Number Column}, [Row Number]@row, {Duplicate Sheet Created (date) Column}, [Max/Collect Column]@row), 1)


    Basically we set it up so that [Column 1] changes trigger a copy row automation. We then use the most recent entry for that particular row number to pull the information from the modified by column.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!