There is a real problem hiding in plain sight here, and it deserves a direct answer: manual tracking of client assessments across shifting rosters is a broken system that no amount of careful copying will fix. The person asking this question has already done the hard part, identifying the exact gap between what they need and what their spreadsheet can do. But the solution they are reaching for, transferring highlights from one list to another, is still anchored in the old way of thinking. They are not looking for a better tool; they are looking for a more efficient way to do the same repetitive work.
What makes this situation so familiar is that it is not about a lack of effort. It is about a workflow built on the assumption that data entry and visual cues are the same as tracking. The yellow highlight on the old list tells them an assessment is done. The new list, pasted directly from the weekly roster, brings in fresh names but no memory of what has already been completed. Conditional formatting can flag whether a name is new or old, but it cannot carry meaning forward. That is the core limitation. The spreadsheet is being asked to act like a database, but it is still being treated like a piece of paper.
The practical takeaway here is that the real solution is not to move the highlight. It is to stop using highlight as the source of truth. What this person needs is a simple status column that stores the assessment state for each client, independent of which weekly list they appear on. Once that status lives in its own field, the weekly comparison becomes a matter of matching names and updating only what changed. The highlight becomes a visual cue, not the record itself. That shift in thinking turns a fragile process into a repeatable one.
For anyone reading this who has felt that same quiet frustration on a Sunday night, staring at two lists and wondering what you are forgetting, this is the moment to step back. The goal is not to make your current spreadsheet slightly faster. It is to redesign the way you capture the information so that the system does the remembering. You do not need a revolutionary tool. You need to move the assessment status out of the formatting and into the data. That is the concrete step. That is the fix.