resettable, over-ridable, default cell values

[Iakovosian]

Okay, so here's the situation; this snapshot is of the relevant area of a multiple worksheet data repository. The two cells of interest are highlighted green for clarity, and the highest visible row is row 1.

• The 'Item Search:' cell accepts a variety of word or phrase inputs, and has data validation to ensure only valid inputs are possible. The data validation is taken from an alphabetised list of possible inputs, and the cell has a drop-down list option (hence the little arrow to its right).
• The 'Stack:' cell uses the input from the 'Item Search:' cell in the following formula...
  =IF(COUNTIF(C3:F315,J6),VLOOKUP(J6,C3:F315,4,FALSE),"~")
  ...where J6 is the 'Item Search:' cell, and the range C3:F315 is the relevant part of a lookup table on the same sheet.

Now, this is what I would like to happen in the 'Stack:' cell...

• Current functionality:
  • When an invalid input is entered into the 'Item Search:' cell, a tilde is displayed instead of a number.
  • When a valid input is entered, the relevant number from the lookup table is displayed in the cell. The 'Buy:' and 'Sell:' cells are also updated in the same fashion.
• Desired additional functionality:
  • In the first instance, the tilde cannot be overwritten.
  • In the second instance, the 'default' number can be overwritten by inputting another number into the 'Stack:' cell.
  • When a new input is entered (or just the same input again) into the 'Item Search:' cell, the default number (or the tilde) is then displayed again.
• Wish list:
  • To have a check-box (or similar; such as a yes/no input in an adjacent cell) that, if ticked, means that the displayed number in the 'Stack:' cell will not be changed/affected by any new 'default value' being read in from the lookup table. The number can still be modified by manually entering a new one.

NB: Whatever value is displayed in the 'Stack:' cell must be readable by formulae in other cells; namely the 'Buy:' and 'Sell:' cells, whose values would become a ratio of the 'Stack:' cell's lookup value and that being displayed in it at that time.

Is this possible to any degree? Preferably (but not exclusively) without needing the use of macros.

Thank you in advance.


P.S. I could probably use more than one cell to achieve the same (or similar) effective functionality (one cell holds the default value, another holds a possible user inputted value, and a third holds the relevant output value), but this would not look as good nor be as intuitive to the end user. This workbook is intended to be distributed to other people with much of it being locked and protected.