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 (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

Dynamic finding and replacing via functions
 

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

Dynamic finding and replacing via functions
 

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

should read:
B3 extracts the Email Address

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

Dynamic finding and replacing via functions
 

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

Dynamic finding and replacing via functions
 

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