Jan 30 2023 08:32 AM
Jan 30 2023 08:32 AM
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.
Jan 30 2023 10:23 AM
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:
Jan 30 2023 12:26 PM
Jan 30 2023 01:18 PM - edited Jan 30 2023 01:19 PM
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.
Jan 31 2023 11:36 AM
Jan 31 2023 11:40 AM
I was just hoping for a quick fix.
And there pretty surely IS a quick fix. Just not the one you had in mind. Yours was a bandaid; I was suggesting a way to cure the infection.