Question: Updating cell A1 should shift its previous value to B1, and the old B1 value to C1. How can this be accomplished without standard IF or LET functions?
try recroding a macro which automates shifting of cells; it isnt true var assignment but bypasses if/let functions and manages the update when a1 changes. might be a neat workaround if you dont mind using macros
I managed a similar need by exploring Google Apps Script as an alternative to function-based solutions. Instead of using IF or LET functions, I set up a simple script with an onEdit trigger that monitors changes to cell A1. When A1 is edited, the script shifts the previous values to B1 and C1 accordingly. This approach gave me more control over the process though it does require some programming knowledge. It was a practical method that worked reliably once the trigger was properly configured, so it might be worth considering for your use case.
I explored an alternative that avoids external scripting by leveraging Google Sheets’ iterative calculation feature. By enabling iterative calculations in the settings, I devised a method where circular cell references help capture and shift values over time. Although setting up these dependencies takes careful planning and is somewhat sensitive to changes in the spreadsheet’s structure, this approach can successfully simulate a shifting register effect. In my experience, it provides a viable solution in controlled environments without relying on functions like IF or LET.
I ran into a similar requirement a while back and ended up exploring an approach that, while not fully automated, leveraged the built‑in version history of Sheets. This method does not shift cell values in real time but allows you to retrieve old values easily since every edit is saved as part of the version history. Essentially, if you need to compare the current value of A1 with previous ones, you can revert to an older version or simply copy values manually when needed. Although this solution lacks automation, it proved useful for tracking and retrieving past data without the need for additional scripts.