Keep historical cell changes record

[StrawberryDreams]

What would be the best way to go about designing a job task tracking scheduler where a Job task name , can show current status and date changes etc. but keep a historical record in another table of all the previous changes. I'm not sure if this is what a gnatt chart does.

Would a typical data entry form that exports all the job task information into a Main Table work ? Then pull up / search for that data entry record and if any changes are made to certain cells then save/submit it as another record even if the Job task name is the same ?

 

[Candyman8019]

My typical approach is to use a Worksheet_change event, so any time a field is changed, it will copy that row to another hidden sheet, along with the date of the change and the userID of who made the change. That way, you can have a chronological history of changes for that record.

 

[StrawberryDreams]

My typical approach is to use a Worksheet_change event, so any time a field is changed, it will copy that row to another hidden sheet, along with the date of the change and the userID of who made the change. That way, you can have a chronological history of changes for that record.

ok so that hidden sheet is just another table etc with all the data, and we're not creating a new hidden sheet with each worksheet change correct ?

 

[Candyman8019]

Correct. It's pretty much a duplicate of your main sheet with regard to columns...It is just an ever growing list of the records that were changed. On that audit sheet you can set auto-filters so you can filter for a specific task and see it's changes over time.

 

[StrawberryDreams]

Correct. It's pretty much a duplicate of your main sheet with regard to columns...It is just an ever growing list of the records that were changed. On that audit sheet you can set auto-filters so you can filter for a specific task and see it's changes over time.

cool thanks. From that Data Table that houses everything if I created an easy pivot table solution from that and lets say I had 4 different job task changes all under the same name ID#, I'm guessing the pivot table would show all 4 ( one per row ) ?

Or I guess get as sophisticated as I want, with more programming show last change and most recent etc.

 

[Candyman8019]

Yeah, you could use a pivot table...or just turn on auto-filter to show only the job task you want to see...and you'd see all changes for it. For example:
Here's a table where I have jobs/tasks listed.

As I change any of those fields, my audit sheet would grow...for example: NOTE: On the audit sheet I record date/time of the change, and who did the change, as well as the changed record.

I can then filter my audit sheet, for example if I want to see how job 456 changed over time...

 

[StrawberryDreams]

Yeah, you could use a pivot table...or just turn on auto-filter to show only the job task you want to see...and you'd see all changes for it. For example:
Here's a table where I have jobs/tasks listed.
View attachment 112205

As I change any of those fields, my audit sheet would grow...for example: NOTE: On the audit sheet I record date/time of the change, and who did the change, as well as the changed record.
View attachment 112206

I can then filter my audit sheet, for example if I want to see how job 456 changed over time...
View attachment 112208

Great thanks Joe, this will be a good start for simple project management, until I decide I need something different. Cheers Thanks.

 

[Candyman8019]

Happy to help.