|As you have come to realize, you can't enter data in a cell and have the previous contents (e.g. the formula) remain. A cell can only contain one thing at a time.|
As for your second question, the answer is No. A formula can't "send" data anywhere. Data can only be "pulled" from one cell to another by using a simple equality formula, e.g. in M25 you enter =E25. Of course, this won't work in your situation because you still have your original issue of wanting to put a formula and a value in the same cell.
If you really want to use the same cell for either the value from a look-up table or from user input, you'll need to use a macro. A Worksheet_Change macro could be triggered by the drop-down and either perform the lookup or ask the user for input.
We can't offer any code just yet because we don't know the specifics of your operation, such as what does the Nested If do, where is the lookup table, etc.
Is it possible that you could accomplish your goal with 2 cells, one for the lookup and one for user input if needed?
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.