Here's a play which uses a sub to place the IM name entered by the user
within the source table's AIM col (Not sure it's possible to do this using
formulas)

An implemented sample is available at:
http://www.savefile.com/files/3744393
Place IM name within AIM col.xls

Source table assumed in A4:D30,
headers* in A4:D4, data from row21 to 30
*LastName, FirstName, Email Address, AIM

In B1 is a DV droplist to select LastName
[created via: Data Validation, Allow: List,
Source: =OFFSET($A$21,,,COUNTA($A$21:$A$30)) ]

In B2: =IF(B1="","",VLOOKUP(B$1,$A$21:$D$30,ROW(A1)+1,0))
B2 copied to B3.

B2 extracts the First Name, B4 extracts the Email Address

The IM name will be entered in B4

Over B5 is a forms button titled: "Place IM"
assigned with the sub PlaceIM below
(sub is placed in a regular module)

Clicking the button will then place the IM name input in B4
into the table's AIM col (in line corresponding to the email add in B3)

'-------
Sub PlaceIM()
Dim rng As Range, Email As Variant
Set Email = Range("b3")
Set rng = Range("c21:c30")
If Email = "" Then Exit Sub
For i = 1 To rng.Count
If rng.Cells(i).Value = Email Then
rng.Cells(i).Offset(0, 1) = Range("b4")
End If
Next i
End Sub
'-------

To implement the sub:
Press Alt + F11 to go to VBE
Click Insert Module
Copy n paste the sub into the code window (whitespace on the right)

Press Alt + Q to get back to Excel

Draw a forms** button on the sheet, and assign the sub "PlaceIM" to it
**from the forms toolbar. Activate the toolbar via: View Toolbars Forms
Re-position / re-size the button to fit over cell B5 (Hold down Alt key)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dand06" wrote:

Hello all,
So, I have an array of data, specifically columns of lastname,
firstname, and emailaddress. I have the user enter their last name
into a validation cell and the two cells below ("first name" and "email
address") are populated with a VLOOKUP function. All very simple. (see
below)

Type Last Name: Smith (user enters last name here; list validation
used)
First name John E. (VLOOKUP populates)
Email address (VLOOKUP populates)

Now, I've added a new column: IMname

What I'd like, is for the user to be able to specify their IM name in a
cell, and use a function (or macro if I *must*) to locate the IMname
cell that corralates to their row and replace the value with the user's
input.

Enter IM name
AIM: jsmith (user enters value here)


<further down in the data array

LastName FirstName Emailaddress AIM
Smith John jsmith (was
blank, now user defined)

Any ideas?
thanks!
Dan


--
dand06
------------------------------------------------------------------------
dand06's Profile: http://www.excelforum.com/member.php...o&userid=34646
View this thread: http://www.excelforum.com/showthread...hreadid=544091