The biological outcomes of a given compound once injested are because of the way that compound interacts with proteins and other structures in the body. There is no general method by which you could reasonably hope to "cancel out" that effect, if you could at all, since it depends on the nature of the interaction.
But by designing an inhibitor of the interaction, you can cancel the effect. This is the basis of much of med chem, hardly a far fetched concept.
So I would say the idea that you can use the structure of a molecule and how it interacts with a receptor to design another molecule to cancel its effect is a routine exercise in med chem. Blocking the signal that produces a certain effect may not produce the opposite effect though. For example, blocking happy signals may not make you sad, just less happy. If that makes sense, it depends on whether the opposite biological response is caused by lack of signal or different signal.
In theory you are correct. If only drug design were so routinely simple (or maybe not; I might be out of a job if it were.) I think what you describe would be considered a subset of ways you might try and target an enzyme for the purposes of inhibition. Consider for example that there is not just one mode of inhibition; you have competitive, non-competitive, uncompetitive, and various hybrids thereof.
I would further argue that library screening is probably used more routinely, since designing drugs in the way you describe would have to be based on information already known about an enzyme's function, it's substrate, the structure of the binding pocket, and the mechanism by which it reacts and binds the substrate. We don't know that much about the vast majority of proteins, hence my answer.
Also, as you say, designing an inhibitor against a particular process doesn't always give you the exact opposite effect. Beyond your own example, what if an enzyme's substrate was something as prevalent as, say, pyruvate?
In any case, I think I interpreted the question a little differently. An analogy by way of simple arithmetic: if I have some positive integer, I can effectively 'cancel it out' with another, negative integer of equal magnitude (as in, 1 + (-1) = 0). It's crude, but that's how I thought the OP was approaching the question. Based on that, my answer was generally no. It is, however, more complicated than that (as we have addressed).