Forum Discussion
Is there a formula/function for once true always true?
I need a way to type an order number into a specified cell and have the "shipped" column say "yes" once the numbers match. The column needs to say "no" until the order number has been entered into the specified cell. But once the number is entered and the values match, yielding a "yes" it needs to stay true, "yes", always.
Also, this document is shared and updated frequently in teams so some functions are limited there.
Is this possible?
I have included screenshots of a simplified example of what I am working on.
Ideally, E2 would remain "yes" even when G2 is updated.
Thanks!
5 Replies
Your request makes sense, but the example given doesn't (or needs better explanation).
What I mean is that the second shot shows cell G2 containing a different order number. WHY would somebody enter that number (23-002) in the row for order number 23-001?? For that matter, why enter an order number there at all, as opposed to entering the Date of the shipment, since that would be important information that doesn't seem to appear any other spot??
So can we back up and get a better sense of the big picture:
- what are you tracking?
- what is the "raw data" that needs to be collected on the INPUT end of things? (anything beyond what is shown)
- what ultimately is the OUTPUT of the process? (reports, etc)
- who are the various parties at the input and output end?
- Thank you for replying. I get that it is confusing. This is just a much simpler example of what we have set up because I am looking for a solution to this specific problem.
The actual data is a log of orders on one sheet. Then there is another sheet that is set up like an order form so when you enter the order number into the unlocked cell it populates all the given data onto a printable form. A third sheet does the same thing for a packing slip (ie. the shipping thing)
Right now we just type "yes" in over "no" once the forms are printed. But that step keeps getting skipped and we have missing forms and/or duplicates of things all the time.
And yes, I get that this is a lot of hassle but we are a small underfunded non-profit, and excel is what we can afford lol. We were looking for a way to make this happen so that it's harder to forget when we have printed order forms and packing slips.
Therefore, the example shows the outlined box as the "order number" cell on the other sheet. that cell is constantly changing. And the only thing I am looking to do is mark "yes" and keep the yes once the number has ever been typed in the reference cell on the other sheet.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.
