Hi,

Try this little bit of code. It is worksheet code so it needs to be placed
in the code for the worksheet where the datalist is.

A word of warning, it breaks down if the cell is cleared with spaces.  

May somebody could comment on a solution. The error occurs when an error
condition occurs in the cell - ie you enter rubbish it can't match it so the
datavalidiation kicks (cancel ok) but retry causes the problem you clear the
bad entry and then the application.enableevents =false causes an error.

The two changes you need to make are the const refences.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
     
   Const cszCell As String = "D1"
   Const cszTable As String = "A1:A10"
   Dim idx As Variant
   
   If Me.Range(cszCell) = "" Or _
       Trim(Me.Range(cszCell)) = " " Then Exit Sub

   Application.EnableEvents = False
   On Error GoTo error_pickup
   
   If Not (Intersect(Target, Me.Range(cszCell)) _
           Is Nothing) Then
       idx = Application.WorksheetFunction.Match( _
           Me.Range(cszCell), Me.Range(cszTable), 0)
       If IsNumeric(idx) Then
           Me.Range(cszTable).Cells(1, 1).Offset( _
               idx - 1, 0).Copy
           Me.Range(cszCell).PasteSpecial xlPasteFormats
       End If
   End If
error_pickup:
   Application.CutCopyMode = False
   Application.EnableEvents = True
End Sub