Forum Discussion
Transferring the contents of a cell to an adjacent cell when it is changed
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.
https://docs.google.com/spreadsheets/d/18TaQo_PvuvXQEqwEttGTcIVsrCZ6lJGk/edit?usp=sharing&ouid=114796826648576103569&rtpof=true&sd=true
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.
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.
- 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.
SergeiBaklan HansVogelaar PeterBartholomew1 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.
- 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.
