Dynamic finding and replacing via functions

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 jsmith@nowhere.com (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@nowhere.com jsmith (was blank, now user defined)

Any ideas?
thanks!
Dan

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 jsmith@nowhere.com (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@nowhere.com 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
>
>

Typo in line:
> ... B4 extracts the Email Address

should read:
> B3 extracts the Email Address

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

That's a great little sub! Exactly what I was looking for! If anyone else can think how to do this via an integrated function (so people don't freak out about giving this sheet Macro permissions), I'd be most appreciative!

But thanks a ton Max!
Dan

You're welcome, Dan !
Thanks for feedback ..

> .. how to do this via an integrated function
Just some thoughts (but do hang around for insights from others) .. Formulas
eg: an INDEX/MATCH placed within the AIM col can only return/populate one B4
value into 1 correct cell in the AIM col at any one time. It cannot then
"freeze" the value there. So when B4 is re-input by the next user (with a
different email in B3), the AIM col formulas will recalc and return the B4
value into another cell correctly, but we would lose the B4 value returned
for the earlier user.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dand06" wrote:
>
> That's a great little sub! Exactly what I was looking for! If anyone
> else can think how to do this via an integrated function (so people
> don't freak out about giving this sheet Macro permissions), I'd be most
> appreciative!
>
> But thanks a ton Max!
> Dan
>
>
> --
> dand06
> ------------------------------------------------------------------------
> dand06's Profile: http://www.excelforum.com/member.php...o&userid=34646
> View this thread: http://www.excelforum.com/showthread...hreadid=544091
>
>