The spreadsheet was never meant to be a time machine. Yet here is a user asking it to be exactly that: a living record that remembers every weekly update, even as the cells above it change. The request is simple, the frustration is familiar, and the solution is not a formula. It cannot be. A formula in Cell C1 will only ever reflect the current state of Cells A1 and B1. The moment you overwrite last week's date and status, the history is gone. That is not a limitation of your skill with spreadsheets; it is a limitation of the tool's design.
What this person actually needs is not a clever function but a shift in approach. They are asking for a log, and a log is not a calculation. It is an event record. The only way to capture past and present inputs in one cell is to write that record somewhere that persists, and that requires either a script or a very deliberate manual habit. The user has already admitted they are not good with scripts, and that is fair. But the alternative is not to accept a broken workflow. The alternative is to understand that the moment you want a cell to remember, you have stopped using a spreadsheet as a calculator and started using it as a database. And databases need a different mechanism.
For this user, the practical path forward is a simple one: stop trying to make Cell C1 do the remembering. Instead, restructure the file so that each week's update lives in its own row, with the date in column A, the status in column B, and the running history visible in column C through a straightforward concatenation of the rows above. That is not a script. That is a table. And a table is something a spreadsheet is actually good at. If they insist on keeping the single-cell format, then yes, they will need to learn a small piece of VBA or a basic Apps Script trigger that appends the current values to a hidden log before overwriting. It is not as intimidating as it sounds, and it is the only formula-free way to achieve the goal they described.
The deeper point here is that most spreadsheet pain comes from asking a tool to do something it was never designed to do. The user is not failing. The tool is. And the fix is not more complexity, it is a more honest structure. So here is the concrete takeaway: if you want history, build a history table. If you want automation, learn the script. But stop expecting a formula to bend the fundamental rules of time and cell references. It will not happen, and it should not have to.