A2’s value is constantly changing. I’d like a ‘snapshot’ of A2 taken as soon as C2 is ticked.
I’ve been told that this is only possible with VBA. Is that really true?
Can you elaborate a little bit more on how this data is set and how you set the checkbox? I might have an easy idea, but it depends on how the data is set.
Thanks for looking into this, Jeff.
The ‘Trigger Snapshot’ checkbox is just for this example. In my actual sheet, ideally the snapshot of A2 would happen as soon as C2 is not empty.
ie, imagine there was a function called “SNAPSHOT()”, then in B2 I would put =IF(C2<>"",SNAPSHOT(A2))
A2’s values are fed by simple multiplication of 3 cells in a different sheet.
I originally thought you could pull the value into a form button and submit it to a sheet, but I’m guessing that’s not what you want. However, I do have another solution. I haven’t played with this much and I don’t know the repercussions, if any, but if you go into your Spreadsheet Settings, you can turn on Iterative Calculations and set the max number of iterations to 1. This allows formulas with circular dependencies to be performed. The number of iterations is set to 1 so it doesn’t run calculations over and over unnecessarily. The only alternative I can think of is a script.
Hi Jeff. Thank you for trying! I’ll look into the form possibility because I’ll need the iterations to be unlimited for the rest of the spreadsheet (if it was specific to one sheet, it would be possible).
It should work. Most people almost never use iterative calcs and circular references. Unless you are doing something really unusual the number of iterations shouldn’t matter. It’s just there to prevent the sheet from getting into a calculation loop that it can’t get it out of.