Can we have fourmola to find amount of changes from a specific user, for specific column?
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!
Best Answers
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
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!
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
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!
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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!
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
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!
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Thank you!!! 😀
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!