Forum Discussion
Is there a formula/function for once true always true?
It sounds to me like you don't have an Excel problem. You have a procedural problem. And though it's possible that a redesign of the Excel sheet could help with straightening out the procedural issues, it's not going to do so by messing with the integrity of the data (which is what you're doing if you freeze a result so it doesn't actually reflect what the original formula was meant to display). You do need to be able to store the result if something actually has been shipped, but you do so by redesigning so that that result stays consistent with the surrounding data that fed into it in the first place, not by freezing a result that then becomes inconsistent with the data surrounding it.
So my questions about that design still stand.
Let me offer an analogy, with the hope that it doesn't offend: I take it from your user name that you are a medical doctor; as such, I'm sure that the integrity of medical records of a patient is of vital concern. Freezing the result of one reading that was favorable--be it blood pressure, white blood cell counts, temperature, whatever--saying in effect "It's been handled"--and not letting the results of subsequent tests be added to the record, modifying the formerly rosy picture: that would not be an acceptable procedure. As a data processing professional (long ago retired), I've learned to take the integrity of data in a spreadsheet seriously as well. Just as you want a medical history to be accurate, the history--in this case of orders and shipments--also needs to be kept in a way such that any subsequent investigation can reconstruct the history.
Can you, without violating privacy or revealing proprietary information, share a copy of the actual spreadsheets involved? You can post them (it?) on OneDrive or GoogleDrive and paste a link here that grants edit access to the sheets. That way we (I or somebody else) may be able to get a better handle on what you're working with.
