How to implement a resettable, over-ridable, default cell value in Excel?

I'm not at my PC right now, so I can't test this but here is what you need to do:

Number 2 in your "Information found so far" is the right direction to go, even though you said that you didn't want macros.

Move your formula for the Stack cell into another cell that isn't being used. Lock this cell, and set the background and text colors to be the same (so that it is "hidden"). For now, let's say that this is in O6. (Or just have this cell on another sheet that they can not access. I often have a hidden sheet just for these.)

Right-click on the worksheet tab, and select View Code. In the new window, double-click the worksheet name that you want this code to run on.

Private Sub Worksheet_Change(ByVal Target As Range) should be the default function that appears (and it will be empty).

Place the following code into the Worksheet_Change routine:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Intersect(Range("J6"), Target)

    'If J6 has not been changed, then exit.  Otherwise continue.
    If rng Is Nothing Then
        Exit Sub
    Else
        'Replace password with the password that you use to protect the sheet (two places)
        ActiveSheet.Unprotect ("password")
        If Range("O6").Value = "~" Then
            Range("M6").Value = "~"
            Range("M6:M7").Locked = True
        Else
            Range("M6").Value = Range("O6").Value
            'Use M6:M7 here instead of just M6 because cells are merged.
            Range("M6:M7").Locked = False
        End If 
        ActiveSheet.Protect ("password")
    End If
End Sub

@SiddharthRout: I will still upload a current copy of the file for your perusal. Parts of my question have been answered, but there are still the two items from my 'Wish list' to be done with yet! –

As per my earlier suggestion, the current code that you are using should be written as

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    If Not Intersect(Target, Range("J6")) Is Nothing Then
        Application.EnableEvents = False
        ActiveSheet.Unprotect ("012370asdf")
        If Application.Evaluate("=IF(COUNTIF(C3:F315,J6),VLOOKUP(J6,C3:F315,4,FALSE),""~"")") = "~" Then
            Range("M6").Value = "~"
            Range("M6:M7").Locked = True
        Else
            Range("M6").Formula = "=IF(COUNTIF(C3:F315,J6),VLOOKUP(J6,C3:F315,4,FALSE),""~"")"
            Range("M6:M7").Locked = False
        End If
        ActiveSheet.Protect ("012370asdf")
    End If

LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

This also negates the use of an extra cell N6.

I am looking at the rest of the stuff now and will update it soon.

UPDATE: Both your requests in the WishList are complete.

Your Worksheet_Change event now becomes this to incorporate Wish List 1 (See Snapshot attached)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vVal As Variant

    On Error GoTo Whoa

    vVal = Application.Evaluate("=IF(COUNTIF(C3:F315,J6),VLOOKUP(J6,C3:F315,4,FALSE),""~"")")

    If Not Intersect(Target, Range("J6")) Is Nothing Then
        Application.EnableEvents = False

        ActiveSheet.Unprotect ("012370asdf")

        '~~> Check the value of the CheckBox and update cells only if false
        '~~> This is valid for "~" as well i.e if the checkbox is Checked then
        '~~> even "~" remain unchanged. If you don't want this, then move the 
        '~~> below condition inside "ELSE" part :)
        If OLEObjects("Checkbox1").Object.Value = False Then
            If vVal = "~" Then
                Range("M6").Value = "~"
                Range("M6:M7").Locked = True
            Else
                Range("M6").Value = vVal
                Range("M6:M7").Locked = False
            End If
        End If

        ActiveSheet.Protect ("012370asdf")
    End If

LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

For your 2nd Wish List I had two options. I went ahead with the 2nd option.

1) Use the method as described in www.ozgrid.com

Topic: Auto Complete Typing in an Excel Data Validation List

Link: http://www.ozgrid.com/Excel/autocomplete-validation.htm

And

2) Use a control instead of DV List. For this, I made these changes in the list

• Remove Data validation in Cell J6
• Gave a "Name" to your list X3:X315 from Name Manager. I called it "List"
• Placed a ComboBox on top of Cell J6 and set the .ListFillRange to the above "List" in design mode
• Added the below code to the worksheet code area

CODE

Private Sub ComboBox1_Click()
    Range("J6").Value = ComboBox1.Value
End Sub

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
    If KeyCode = 13 Then
        Range("J6").Value = ComboBox1.Value
    End If
End Sub`

Now your ComboBox will auto-complete whenever you type anything in the box.

SNAPSHOT

SAMPLE FILE LINK (This link is active for 7 days)

Sample File

HTH

Sid