Record the cell changes

Question

Hi All,We track retention status of each employee in a excel sheet. Time to time this retention status changes based on latest update. So a particular cell value changes time to time.Can we track these changes and add those records against the employee monthly?, in a separate sheet? For example Employee A Jan 23 Status, Feb 23 Status.&nbsp;With this I can develop a historical track of his retention status.Seeking for a guidance in building this. Thanks in advance.&nbsp;

mathetes · Answer

NaradaJay95&nbsp;&nbsp;Can we track these changes and add those records against the employee monthly?, in a separate sheet?&nbsp;Of course. Exactly how, what method, etc., will depend to a great extent on how you organize and keep your data already on other aspects of employee history.&nbsp;Is it possible for you to create a mockup file -- no real names or other identifications -- that gives us an idea of&nbsp;your starting point on&nbsp;computerized records. Post a copy of that file on OneDrive or GoogleDrive and paste a link here granting edit access.

patrick2788 · Answer

You could use the Show Changes feature:https://support.microsoft.com/en-us/office/get-help-with-show-changes-in-excel-a1493bf9-25c3-470a-b970-60eceb0136e5

mathetes · Answer

NaradaJay95&nbsp; &nbsp;I was responding as the former Director of the HR/Payroll database for a major US corporation.&nbsp;Patrick2788&nbsp;gave you a feature of Excel that tracks changes to a spreadsheet. If that's what you're seeking, that's fine by me.&nbsp;My reading of your question, though, comes from my own background: I was assuming you were really seeking the most effective ways to keep what would amount to "Employee History," specifically in connection with "retention status".....but quite possible Employee History as it applies to all kinds of status changes, from hires to promotions, to salary changes, through to termination or retirement. That's also why I asked that you post a copy (without real names) of what you have now in place as an employee database. There's no reason, I assume, for us to design something "from scratch."

naradajay95 · Answer

mathetes&nbsp;.&nbsp;Emp IDEmployee NameCurrent Risk Status - Retention1Employee ANo Known Risk2Employee BLow Risk - 1 - 2 year3Employee CMedium Risk - Within 1 year4Employee DHigh Risk - Immediate&nbsp;Current Risk Status changes time to time based on the latest update of the risk. For example Employee A can be in "No Known Risk" in the month of Jan and then changed to "Low Risk - 1 - 2 year" in the month of Feb.&nbsp;So I need to track those changed monthly in a table like below.Emp IDEmployee NameJan-23Feb-231Employee ANo Known RiskLow Risk - 1 - 2 year

mathetes · Answer

NaradaJay95&nbsp;&nbsp;You need (or at least it would be greatly preferable) to give a bit more information here. What you're displaying is what I'd call OUTPUT from a tracking system. You have two output reports,the first one showing the current status of all employees,the second showing the history of a single employee.&nbsp;What records do you have, currently, &nbsp;that would enable the production of those reports? What kind of database exists already of your employees? Or is there none, and that's what you're seeking?&nbsp;What's the INPUT that can serve as the basis for the database on which these two reports could be generated? Said another way, how do you determine--what happens that makes a change, how do you learn about it, do you learn after the fact of the change from the employee or is it something determined by outside agencies, etc. etc.&nbsp;These questions may not make sense, I suppose. But unless we have the answers, unless we know what the overall process is, from event to input to database to report, then I'm not sure what help Excel could be other than giving you rows and columns to display what you show.&nbsp;Now if you're saying that the first table in your posting is the INPUT and Database, rolled into one, then what you need to do is modify it to include the date that that status changes,&nbsp;DateEmp IDEmployee NameCurrent Risk Status - Retention1/2/231Employee ANo Known Risk1/4/232Employee BLow Risk - 1 - 2 year1/15/233Employee CMedium Risk - Within 1 year2/1/231Employee ALow Risk - 1 - 2 year1/31/234Employee DHigh Risk - Immediate&nbsp;But I want to go back to my questions regarding the INPUT: please describe what determines that status in the first place, how it comes about, who determines it, when you are informed, etc &nbsp; [And just for context, what are we talking about anyway? COVID risk? What? What is "retention"?] &nbsp; And, as I asked before, what other records do you keep on employee status? Or are you not involved in this from a personnel/HR kind of role (where people would track pay class, pay rate, employment date, promotion dates, etc?).&nbsp;Again, the reasons for these questions: you're not keeping these records on risk/retention in a vacuum: it helps in designing a process to know the bigger context vis-a-vis records that are kept.

Emp ID	Employee Name	Jan-23	Feb-23
1	Employee A	No Known Risk	Low Risk - 1 - 2 year

Forum Discussion

Record the cell changes

5 Replies

Resources

Emp ID	Employee Name	Current Risk Status - Retention
1	Employee A	No Known Risk
2	Employee B	Low Risk - 1 - 2 year
3	Employee C	Medium Risk - Within 1 year
4	Employee D	High Risk - Immediate