Forum Discussion
Highlight changes to results of VLOOKUP
Is there a way to highlight what values have changed each time I open a customer tracker?
In order to highlight a change, we would need to have a way to recognize that change; usually that would be accomplished through some form of time-stamp on the record.
So can you tell us how your "master tracker" spreadsheet works? Do you, for example, keep the records with a date stamp on them, so that one could identify a change based on comparing the last (current) record with the most recent prior record?
Would it be possible for you to post a copy (after removing identifiable customer IDs) of your workbook, posting it on OneDrive or GoogleDrive, with a link pasted here that grants us edit access to that workbook?
Thanks so much for your reply. Basically the Master Tracker is a huge spreadsheet that tracks a large number of orders being manufactured abroad and imported to the UK. This tracker is updated (by various people at various times) any time we receive updates such as production delays, vessel delays etc. This spreadsheet also looks up data from other sources (shipping port data, transit time data etc) so don't think it is easy to share.
Each customer has their own tracker that lists just their orders and some limited information pulled from the master tracker (such as shipping dates, docking dates etc). Once a week we open the customer tracker which uses VLOOKUPs to automatically update changes to docking dates etc. We save a copy named with the current date and send it to the customer. So we always know the date that the customer tracker was last edited, and want to highlight anything that has changed since then.
So at the moment, we manually compare the customer tracker to the copy that was sent to them last week (which has all formulas converted to values). We then highlight those changes a different colour to draw the customers attention to the changes.
I'm not sure if I will be permitted to share the documents - does the information that the Master tracker changes frequently but the customer tracker is only updated once a week help, without having to share the spreadsheets?
I appreciate your time 🙂
Sally
It's hard to know how to proceed. There are automated ways to compare spreadsheets (accessible only with high-end versions of Excel), so it might be possible for you to use one of those methods and IF the only thing that changes week to week in your customer reports is the value in some cells, that should be fairly straight forward. But to the extent that you're adding rows or columns, deleting rows or columns, that gets more complicated. (I have no experience with those methods; only know they exist.)
Let's see if others with experience comparing sheets have suggestions.
You didn't really answer my question about whether or not the data in the master tracker is date-stamped. To the extent I could offer help, it would be dependent on whether that is the case.
Thank you for your reply again. With regards to the Master Tracker being date stamped - sorry I didn't clarify. Various users make changes to this document at various times and I'm afraid there is nothing to record when these changes are made.
It may be that this is something we need to carry on doing manually - it's not a huge job, I was just asked if there was an easy way to show what has changed without this manual comparison. Perhaps the answer I need to give is "no, there's not an easy way!" 😄
Thanks so much for your time
With regards to the Master Tracker being date stamped - sorry I didn't clarify. Various users make changes to this document at various times and I'm afraid there is nothing to record when these changes are made.
Well, as a person who was at one time the director of the HR/Payroll Database of a major corporation, I can only advise that keeping track of history is (or should be) built into any well designed master database. Among other things, doing so makes it possible to go back and reconstruct sequence of events, go back and correct erroneously entered changes, and so forth. That's true of any kind of database, whether it's keeping track of products, raw material inventories, people, trucks.... From such a transactional database, it's also possible to extract a "current snapshot" of who is where or what is where, and so forth.
So I'd encourage you (your organization) to investigate the design of a more robust system.