text replacing

macdonaldf · Mar 30th, 2002, 12:44 PM

Went through the first 15 pages or so until my eyes started to hurt, couldn't find an answer, although it probably is in here somewhere. Anyway, here is my situation.

I have 2 columns, 1st column list initials of person, 2nd column lists persons full name. I want to be able to enter into another cell the initials, and have it give me the person's name in that cell.

i.e. I punch in 'jd' ... when I hit enter, it replaces 'jd' with 'John Doe'

Thanks for any help you all can provide.

Dave Patton · Mar 30th, 2002, 01:09 PM

try Lookup or Vlookup

With Lookup info must be in ascending order.

Vlookup is used most often. See Help.

With a table named rL

ABC ABC info
DEF DEF info

assuming code in A2

=vlookup(a2,rL,2,0)or =vlookup(a2,rL,2,False)

the 2 means look in second column
the o or False means you want an exact match

HOTDOG · Mar 30th, 2002, 01:14 PM

Just to add to Dave's answer, If 2 or more people have the same initials then Vlookup will only return the name for the first set of initials it finds.

eliW · Mar 30th, 2002, 01:31 PM

Quote:

On 2002-03-30 11:44, macdonaldf wrote:
Went through the first 15 pages or so until my eyes started to hurt, couldn't find an answer, although it probably is in here somewhere. Anyway, here is my situation.

I have 2 columns, 1st column list initials of person, 2nd column lists persons full name. I want to be able to enter into another cell the initials, and have it give me the person's name in that cell.

i.e. I punch in 'jd' ... when I hit enter, it replaces 'jd' with 'John Doe'

Thanks for any help you all can provide.

If I understand the question correctly, macdonaldf wants to input initials in a cell and get the entire name in the same cell.
Can it be done by vlookup?
Eli

Mudface · Mar 30th, 2002, 01:45 PM

Quote:

If I understand the question correctly, macdonaldf wants to input initials in a cell and get the entire name in the same cell.
Can it be done by vlookup?
Eli

No, you'd need either an extra column if you wanted to use VLookup or a possibly very long Change macro, if you wanted it in the same cell.

Dave Patton · Mar 30th, 2002, 01:50 PM

Sorry if Vlookup is not appropriate.
I read the part about 2 columns but I guess I did not read the part about the "same" cell.

With the millions of cells available, I wonder whey 2 cells cannot be used.
The code column could always be hidden later.

You could also look at defined names.

[ This Message was edited by: Dave Patton on 2002-03-30 12:52 ]

Tommy Bak · Mar 30th, 2002, 03:22 PM

You could insert this code in the sheets own module (rightclick on the sheettab, choose view code)
Whenever you type/change D2 it will make a vlookup in A1:B100 and return the value from column B. This is not perfect and personally i would do like the others suggested.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo finished
If Target.Address = "$D$2" Then
Target.Value = WorksheetFunction.VLookup(Target.Value, Range("A1:b100"), 2, 0)
End If
finished:
End Sub

regards Tommy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 30th, 2002, 12:44 PM	#1
macdonaldf New Member Join Date: Mar 2002 Location: Detroit Posts: 1	Went through the first 15 pages or so until my eyes started to hurt, couldn't find an answer, although it probably is in here somewhere. Anyway, here is my situation. I have 2 columns, 1st column list initials of person, 2nd column lists persons full name. I want to be able to enter into another cell the initials, and have it give me the person's name in that cell. i.e. I punch in 'jd' ... when I hit enter, it replaces 'jd' with 'John Doe' Thanks for any help you all can provide.

Mar 30th, 2002, 01:09 PM	#2
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	try Lookup or Vlookup With Lookup info must be in ascending order. Vlookup is used most often. See Help. With a table named rL ABC ABC info DEF DEF info assuming code in A2 =vlookup(a2,rL,2,0)or =vlookup(a2,rL,2,False) the 2 means look in second column the o or False means you want an exact match

Mar 30th, 2002, 01:14 PM	#3
HOTDOG New Member Join Date: Mar 2002 Location: Northern Ireland Posts: 6	Just to add to Dave's answer, If 2 or more people have the same initials then Vlookup will only return the name for the first set of initials it finds.

Mar 30th, 2002, 01:50 PM	#6
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	Sorry if Vlookup is not appropriate. I read the part about 2 columns but I guess I did not read the part about the "same" cell. With the millions of cells available, I wonder whey 2 cells cannot be used. The code column could always be hidden later. You could also look at defined names. [ This Message was edited by: Dave Patton on 2002-03-30 12:52 ]

Mar 30th, 2002, 03:22 PM	#7
Tommy Bak Board Regular Join Date: Feb 2002 Location: Guderup, Denmark Posts: 287	You could insert this code in the sheets own module (rightclick on the sheettab, choose view code) Whenever you type/change D2 it will make a vlookup in A1:B100 and return the value from column B. This is not perfect and personally i would do like the others suggested. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo finished If Target.Address = "$D$2" Then Target.Value = WorksheetFunction.VLookup(Target.Value, Range("A1:b100"), 2, 0) End If finished: End Sub regards Tommy