Maybe you could use an event macro:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myTable As Range
Dim res As Variant

'one cell at a time
If Target.Cells.Count 1 Then Exit Sub

'only in column A
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

With Worksheets("sheet2")
Set myTable = .Range("a:e")
End With

On Error GoTo ErrHandler:

res = Application.Match(Target.Value, myTable.Columns(1), 0)

Application.EnableEvents = False
If IsNumeric(res) Then
Target.Offset(0, 1).Value = myTable(res).Offset(0, 1).Value
Else
With Target.Offset(0, 1)
.ClearContents
.Select
MsgBox "Please enter something in: " & .Address(0, 0)
End With
End If

ErrHandler:
Application.EnableEvents = True

End Sub

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

wrote:

Hey everyone,

It took me about 5 minutes just to come up with a subject line for my
question.. I just hope that my question itself won't be too confusing
to answer..

I work for a payroll company and have been assigned to create new forms
that will be used by payroll clerks then submitted to data entry
clerks. The way they work is I have one cell (A1) for inputting an
employee identification number (Empl_ID) and another cell (B1) for
retrieving the name of the employee (Empl_Name) from a table of values.

What I need to do is after the user inputs the Empl_ID in A1, then B1
will automatically populate with the Empl_Name from the table of
values. However, if it does not find the Empl_ID in the table, then
the user will have to manually input the Empl_Name in B1.

The simple solution is just to stuff a VLookup in B1, but if the
Empl_Name result comes up as #N/A, I don't want the user to overwrite
the VLookup formula in B1.

I know this is simple, but I've been RTFM'ing for the past 3 days and
just can't come up with the proper formula.. Any help or solutions
would be graciously appreciated.

Thanks very much,
Richard Garrett

--

Dave Peterson