Dynamic finding and replacing via functions

[dand06]

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 (e-mail address removed) (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 (e-mail address removed) jsmith (was
blank, now user defined)

Any ideas?
thanks!
Dan

 

[Guest]

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 A430,
headers* in A44, 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)

---

 

[Guest]

Typo in line:

... B4 extracts the Email Address

should read:

B3 extracts the Email Address

---

 

[dand06]

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

But thanks a ton Max!
Da

 

[Guest]

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.

---