Formula: Which User Last Modified: Column 1 row 2

A Rose

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!

Paul Newcome

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.

Marco B

Good afternoon

I have the same challenge, I would like to see for an audit topic who specifically made the change in "X" cell of the sheet instead of the entire Row.

Considering that the previous comments were from 2021 and now we are in 2024, is there any formula that have created or any simpler workaround to be able to visualize this in a column instead of check the cell history of the cell by right clicking the cell?

Paul Newcome

@Marco B This is still the way. Basically creating a copy row automation when the column changes and then using a formula with cross sheet references to pull the static data from the copy sheet.

Marco B

@Paul Newcome,

I tried your suggested approach, but unfortunately, it did not work as expected. Instead of copying the rows, they were being moved, which was not our intended outcome. The reason behind this approach was to avoid generating overload in the file.

To help clarify my requirement, I have attached a screenshot below. As you can see, in Row# 20, it indicates that the last user to modify the entire row was a "Smartsheet automation" on "07/24/24". However, what I actually need is to track the last user who modified only the column titled "Supervisor's 1st status". According to the "View Cell History," it was the user "Suly He" who modified this column on 12/07/24.

In order to address this requirement, I would appreciate your assistance in finding a solution that allows us to display the name of the last user who modified the "Supervisor's 1st status" column (Suly He) in a single cell. This will greatly help us track the activity related to this specific column without affecting the other rows or causing any file overload.

Paul Newcome

@Marco B Below is a more detailed version to include which helper columns are needed and whatnot. It is outlining how to capture the "when", but it can be used in the same way to capture the "who".

https://community.smartsheet.com/discussion/63561/date-time-stamp-option

Tanya B

Hello Paul, I followed the instructions from 1/7/21 above got to the final formula (=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)

Thoughts?

Paul Newcome

What do you have populating the [text 1] column? Your error message is coming from the fact that you are looking for a {CREATE DATE} that matches "0".