Urgent please

[Guest]

Hi,

I have Employee name and employee number in one sheet.
In another sheet or another file if i type any employee name it shoud give
me employee number. I dont want to use Vlookup formula.
Is there any other way like validation or something else.
please help me...

 

[JE McGimpsey]

Validation won't do a lookup.

It's hard to know what to suggest, since a VLOOKUP() or something
equivalent [like an INDEX(MATCH()) combination] is nearly always the
appropriate technique to do what you're asking.

Why don't you want to use VLOOKUP()?

 

[Guest]

Some people are hesitant to use vlookup because they think it’s too
complicated. But it’s not that hard to figure out, and it’s a powerful tool,
and in the end you’ll be glad you took the time to learn it.

 

[Guest]

If it for security or something else you dont want to use Vlookup()?
If it is for Security, You could potencially use a macro on your system
which would allow you to extract the Employee number and paste it into the
cell. If only you had access to the macro and database, then others would
not have to deny external file data updates to the data base when they open
the workbook.

 

[Guest]

I am very much perfect in VLOOKUP().
But I don’t want to show formulas in my excel sheet form. The form which I
am creating is multi user form.
The person using this form not aware of employee number. If he enter name,
he should get corresponding employee number.
This form keep on passing to different person.
Please help me if any body has solutions or macros

 

[JE McGimpsey]

One way:

Put this in your worksheet code module (right-click on the worksheet tab
and choose "View Code":

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sPATH = "<your path here>"
Const sFILENAME = "SourceBook.xls"
Dim wbTarget As Workbook
Dim rTarget As Range
Dim bClosed As Boolean
With Target(1)
If Not Intersect(.Cells, Range("EmployeeName")) Is Nothing Then
Application.ScreenUpdating = False
On Error Resume Next
Set wbTarget = Workbooks(sFILENAME)
On Error GoTo 0
If wbTarget Is Nothing Then
Set wbTarget = Workbooks.Open(sPATH & sFILENAME)
bClosed = True
End If
Set rTarget = wbTarget.Sheets(1).Range("A:B")
Application.EnableEvents = False
Range("EmployeeNumber").Value = Application.VLookup( _
.Value, rTarget, 2, False)
Application.EnableEvents = True
If bClosed Then wbTarget.Close SaveChanges:=False
End If
Application.ScreenUpdating = True
End With
End Sub

this assumes that the lookup table is in another workbook/file, and that
your input cell is named "EmployeeName" and you output cell is named
"EmployeeNumber".