Can we have fourmola to find amount of changes from a specific user, for specific column?

A Rose

Hi There,

I have a smartsheet that has multiple columns, including a column of assigned users,

is there a fourmola to check how many changes a specific user made to this column today?

Please help out.

Thank you in advance!

Mark Cronk

Hi @A Rose ,

@Andrée Starå is the person I'd recommend and he's so efficient that he's already responded. Amazing.

I have 1 other idea but it will require regular manual processing and likely some complex report formulas. You'll have to really want this information. The sheet activity log contains record of all changes. You can filter it by date, collaborator or change type and download it. Find the right set of filters, download it, and then import it back into Smartsheets so you can run reports from it. You'll need to do periodic manual updates to get current activity imported. Try setting a few filters and download an activity log. See if it gives you the information you're looking for.

Sorry I can't be more helpful. This is beyond my skill level.

Mark

Mark Cronk

Yes, that's the manual process. You'll need to periodically download the new data and import it into your master sheet in smartsheets. That's a fairly easy process but someone has to do it.

Good luck,

Mark

Mark Cronk

Hi @A Rose ,

You can check the current status of how many times a user was the last person to change a row. You would add 2 system columns: Modified (Date) and Modified By. The columns track the last time a row was modified and who made the change. You'll need to add a 3rd column [Modified Date] with the formula =DATEONLY([Modified]@row). The system modified column contains a time stamp that causes some formulas to error. The DATEONLY function reduces it to just a date. You'll use the Modified Date column as a range in your COUNTIF formulas.

To determine how many times a person was the last person to modify a row you'd use a COUNTIFS function. The syntax is: COUNTIFS( range1, criterion1, [ range2,​criterion2,​... ])

Your formula would be: = COUNTIFS([modified date]:[modified date], Today(), [Modified By]:[modified by], "Arose@smartsheet.com"). You'll replace "Arose@smartsheet.com" with the name of the person you're counting as it appears in the Modified By column.

Does this help?

Mark

A Rose

Hi @Mark Cronk ,

your fourmola  = COUNTIFS([modified date]:[modified date], Today(), [Modified By]:[modified by]

works fine for when i want to check in general how many times a specific user made changes to the entire sheet,

But is there a fourmola to check a specific column, what user made how many changes to Column "A"?

Please let me know.

Thank you!

Mark Cronk

Good morning @A Rose ,

Unfortunately, I don't think there is a way to get what you want through reporting or automation. The data is available in the cell history. You can right click on the cell and view that history. I don't think a common user has access to that data through formulas and reporting.

There are others in this community that have far more backend system knowledge than I do. Maybe one of them will chime in.

Mark

A Rose

Hi @Mark Cronk ,

Thanks for the update,

Do you know if admin access has any other option other then Cell History?

And if you knoe anyone that can help out, who are more advanced, would you mind Please including their name in this thread?

Thank you Mark!

Andrée Starå

Hi @A Rose

To add to Mark's excellent advice/answer.

I think it could work with the copy row workflow, so each time a change to a specific column happens, it would be copied to another sheet.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

A Rose

Hi @Andrée Starå ,

if you want to lookup multiple cells with all it's history, it wouldn't be possible to create another sheet for that,

Although that's a great idea for columns with just a few dropdown options!

Anyone else to help out?

Thank you!

Mark Cronk

Hi @A Rose ,

@Andrée Starå is the person I'd recommend and he's so efficient that he's already responded. Amazing.

I have 1 other idea but it will require regular manual processing and likely some complex report formulas. You'll have to really want this information. The sheet activity log contains record of all changes. You can filter it by date, collaborator or change type and download it. Find the right set of filters, download it, and then import it back into Smartsheets so you can run reports from it. You'll need to do periodic manual updates to get current activity imported. Try setting a few filters and download an activity log. See if it gives you the information you're looking for.

Sorry I can't be more helpful. This is beyond my skill level.

Mark

A Rose

Hi @Mark Cronk ,

You are good!

Seems like we are getting closer to the solution, before we get there, i want to clerify the following,

once i upload back in smartsheet, i'll have the data that was incuded in the download report, so what happens after that date, will the only option be to download another report for example every Month, and then copy and paste the information to the old sheet?

Thank you!

Mark Cronk

Yes, that's the manual process. You'll need to periodically download the new data and import it into your master sheet in smartsheets. That's a fairly easy process but someone has to do it.

Good luck,

Mark

A Rose

@Mark Cronk

Thank you!!! 😀

Andrée Starå

@A Rose

You're more than welcome!

Happy Holidays & Happy New Year!