Transferring the contents of a cell to an adjacent cell when it is changed

Copper Contributor

Hi, first time poster here, so hopefully I’m not about to ask for the moon on a stick – apologies in advance if my query is asking too much.

I have seen a number of other queries similar to my own, but none of the solutions actually fully fit my criteria and I don’t have the VBA skills to modify those that are close so that they work. Hopefully, someone will be able to point me in the right direction…

I have a report that is produced weekly, and that report contains the progress on 17 subjects, most of which don’t progress week on week. I have to produce this report against 20+ clients and most of my time is being spent typing the same “no update” update… As a result, I’ve turned to Excel and put each clients report into a separate worksheet. Now what I want to do, is work within the individual subject boxes (there are a few tick boxes to complete for each subject etc) and where that subject has moved from “In Progress” to “At Risk” I want to record that status change and effectively timestamp the week number that it changed so that that update can be automatically pulled into the corresponding weeks report.

I really wanted to be able to follow the audit trail of a subject also over the past 6-12 months rather than only seeing the previous status and the current one. Is this possible please?

11 Replies



What you're asking is almost certainly possible. One of the biggest factors in making it readily/easily possible is the matter of how your raw data are organized. 


Toward that end, I wonder if it would be possible for you to post a copy of your workbook (after changing any names to the names of, say, Disney or Star Wars characters), so that we could see how you've organized things, and possibly make suggestions on how to re-organize (if needed) and then how to achieve the kind of tracking of status changes. Post the file(s) on OneDrive or GoogleDrive and then paste a link here that grants access.

@mathetes Thank you for your response - I've spent some time "cleaning" my report spreadsheet so that I can share it on here without any repercussions at work, but I can't find a means to upload it - any ideas?



See the last sentence in my prior message. You will be able to post it on OneDrive or Google Drive and then paste a link here that grant access to the file.

@mathetes  - apologies for missing your direction in your first response - thank you for clarifying.


Reporting Spreadsheet 


So in this workbook, its the cells that I've highlighted in yellow, along with similar cells located in M4:M10, X4:X10 and AI4:AI10 that I wanted to track the change of. It doesn't matter too much to me where the audit trail is held, as long as it is on the same worksheet and it doesn't interfere with the existing tables that are already in the worksheet.


What I want to be able to do, is run a query to find the 'latest' update in a given weeknum (current weeknum) and return a True if it differs from the previous weeknum, and a False if it doesn't...


Thanks for your time and patience.


@Sergei Baklan @Hans Vogelaar @Peter Bartholomew @NikolinoDE @Patrick2788 @mtarler 

Oh, my. Now that I've looked at what you have, I'm going to have to beg off the project. I'm sorry to have to say that, but if I were to stay with you, I'd be asking you to go back to Square One, which I strongly suspect, you'd not want to do. 


In my first response to your original inquiry, the second sentence was this:

One of the biggest factors in making it readily/easily possible is the matter of how your raw data are organized. 

And now that I've seen how your raw data are organized, well, let's just say I would have gone about tracking progress on different projects for different clients in an altogether different manner.

(I can see why VBA--which I generally avoid--is what you have in mind, because essentially you've modeled this (I think) on a highly manual project tracking paradigm, perhaps how we'd track each project on separate sheets of paper, ticking off as steps are completed, etc., and then from each sheet take current status indicators to some separate high level summary.  My approach would be to create more of a single transactional database to record for any and all projects as steps are completed, and then use Excel's marvelous abilities to extract and summarize current status from that single database.)


So, by adding their IDs at the top, I'm hoping to call in some of the more VBA-skilled folks on these boards to see if any of them can be enlisted. I'm also attaching an Excel version of the Google Workbook you shared to save others the time of going to Google and downloading it.


I wish you well.


Oh dear, I am staying well out of this...


The two features which might be of use here:


Show changes:

Get help with Show Changes in Excel - Microsoft Support


Provides an extensive list of edits made to a workbook. Must store the workbook in the cloud.  The 'audit trail' is not displayed in sheet but rather in the show changes pane. Available in Excel for Web and some versions of Excel 365.


Spreadsheet Compare (aka Inquire's Compare Files)

A quick way to obtain the differences between two different Excel workbooks. It's very robust but you'd have to run it manually and also maintain versions for the workbook.

Basic tasks in Spreadsheet Compare - Microsoft Support

Thank you Mathetes - I suspected that what I was asking was a bit of a punt - I'm sure that there would have been simpler ways as you suggest to address this from the start. Thanks for the honest response anyway, I'll see if any of the learned colleagues you've tagged in can provide a lifeline, else I'll just accept I've got to look at less automated options.
Thank you Patrick - I did look at the audit options before I posted my query, unfortunately, unless I've not understood them fully, whilst they would provide some level of audit trail, it would still require a level of manual cross referencing which I'm trying to avoid.

Given Mathetes comments above however, it may be a next best option, so thank you for your response.



...else I'll just accept I've got to look at less automated options.


That wasn't my point, certainly. Your approach from the start was "automated" primarily through the use of VBA, automating what were otherwise really manual steps. I sometimes call that type of use "the brute force" approach to automating. I've seen it happen when IT folks know procedural languages (like VBA) that they'll "automate" a process via VBA or a macro, when it could be approached via built-in Excel functions and operate faster and more reliably. It's more a matter of the mindset with which one approaches the task.


I learned early on in my computing days (which were more than 50 years ago, so we're talking pre-PCs) to think of data more in terms of tables of data--sometimes two-dimensional tables, sometimes multi-dimensional. And then one applied the computing power of whatever system existed to extracting/summarizing/cross-tabulating the data from those tables by means of formulas.


Later in my career, even though I wasn't technically in the IT division of my company, I became the director of a project to develop an HR/Payroll database system. I was offered (and accepted) the job, because I could "speak" database "languages," because I understood the concepts. At the risk of over-simplifying things, let me describe one of my primary learnings from all this, whether we're talking of tracking status of projects or people: As humans, coming at such a task, we can easily be tempted to track the employment history of a person by creating a history table for each single person, noting dates for each transaction--hire, promotion, transfer, demotion, promotion, leave, return, retirement, etc--and doing the same for each person in the organization. But it actually is far more efficient to have a single table that tracks all that for every employee, and learn to write queries or reports that extract the most recent status for each, by location, by division, by classification, etc.  Looking at such a comprehensive table with our eyes, it's bewildering; but by using the computer to do the first (and maybe second and third) look, by sifting and sorting according to defined criteria, you can extract meaningful summary data from that single transactional database.


The mistake (if I may be so bold) that you've made is to approach this so that it looks neat (and you've even made it pretty) at the raw data level (at the Input end of things).  But what you could have done is make it be comprehensive at the raw data level, to make sure you are capturing all relevant data on each status update for each project etc, and then use Excel's fantastic functions (e.g., FILTER, XLOOKUP, Pivot Tables, etc) to extract data for Output purposes, creating those nice looking status reports at the end of the line. Excel is truly remarkable in what it can do when it comes to data manipulation; I would go so far as to say only rarely is VBA needed.


I don't know if this would help, but certainly there have been books written on using Excel in connection with project management. Here's one example--I have no direct experience with it, just showing it as an example. There are probably templates as well. Perhaps others can make suggestions along those lines. My goal here was simply to try to point you in one direction that you might want to consider,  hopefully a direction that could end up being more highly automated, not less, taking more full advantage of Excel.

@JPL_76  I have looked at your sheet and not sure I understand it and what you need but I think I do.  Here is my thought:   In the lower sections you have various breakdowns including a section called "Updates".  Why not just have a 'log' in that updates section or add another similar section where each week you log the status.  The existing "Status" field would then be a lookup of the most recent updated in that logged section.