Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Urgent please
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... |
#2
|
|||
|
|||
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()? In article , "vishu" wrote: 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... |
#3
|
|||
|
|||
Some people are hesitant to use vlookup because they think its too
complicated. But its not that hard to figure out, and its a powerful tool, and in the end youll be glad you took the time to learn it. |
#4
|
|||
|
|||
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. "vishu" wrote: 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... |
#5
|
|||
|
|||
I am very much perfect in VLOOKUP().
But I dont 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 "bj" wrote: 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. "vishu" wrote: 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... |
#6
|
|||
|
|||
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". In article , "vishu" wrote: I am very much perfect in VLOOKUP(). But I dont 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
URGENT: Excel files renamed to HEX | Excel Discussion (Misc queries) | |||
Urgent Urgent Urgent!!! | Excel Discussion (Misc queries) | |||
URGENT Please... new worksheet with copied formats but no data. | Excel Worksheet Functions | |||
Linked sheets - URGENT! | Excel Worksheet Functions | |||
Urgent help needed: IF function | Excel Worksheet Functions |