Forum Discussion
Highlight changes to results of VLOOKUP
Hi
I have several spreadsheets (customer trackers) that use VLOOKUP formulas to update data from a master spreadsheet (master tracker). Is there a way to highlight what values have changed each time I open a customer tracker? So if a value is changed in the master tracker, next time you open the customer tracker the data that has changed is highlighted so the customer can quickly see what has changed?
Thanks
Sally
7 Replies
- 'Show Changes' is a possible solution, if available in your version of Excel (I believe it's available in the 'Current' channel and Excel for the web at the moment).
https://support.microsoft.com/en-us/office/get-help-with-show-changes-in-excel-a1493bf9-25c3-470a-b970-60eceb0136e5- Thank you, I will look into this!
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.
