How to record in adjacent cell what my drop down selection was, as a Hard Record formula?

Question

Is there an Excel function coding which can record in an adjacent cell what my drop down selection was From cell A3, into Cell B3, but to keep this even if I remove the drop down selection at a later date as a hard record?

Answer 1

Untested code, record cell A3 (the list selector) into B3:

Sub MemorizeCellA3
Range("B3").Value= Range("A3").Value   ' might need to be .Text instead
Range("A3").ClearContents 'will clear out A3
end sub

Assign it to a button on the sheet.

Source of code

Answer 2

If you want to do this automatically, put this macro in the worksheet module of the sheet that you want to do this on

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, Range("A:A")) Is Nothing Then
    If target.Offset(, 1) = "" Then
        target.Offset(, 1) = target
    End If
End If
End Sub

The range("A:A") is the range you'd like to record. If that changes, and the cell next to it by 1 column, offset(,1), is empty, put the selected value there. If it's not in the range or the cell next to it already has a value, it does nothing.

Answer 3

The cells can either be linked or not. Options to freeze the cell would be

• Copy and paste value
• vba macro that freezes the cell when certain cells are updated.