Home |
Search |
Today's Posts |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding, Replacing and Cut & Paste | Excel Worksheet Functions | |||
Stopping autoformat when replacing cells. | New Users to Excel | |||
Finding and Replacing a "?" | Excel Discussion (Misc queries) |