| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 53957 | 2005-02-01 00:51:00 | "XL brainteaser" | B.M. (505) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 320001 | 2005-02-01 00:51:00 | Here’s a little brainteaser for you XL gurus. I have, say, 500 names and addresses in Column “A” on XL spreadsheet. (An address Book) Now, I want to start typing an entries name in another cell and it turns up the nearest match in yet another. Just like the Windows - Help / index / search facility. Cheers Bob |
B.M. (505) | ||
| 320002 | 2005-02-01 00:57:00 | Hi Bob, Im not clear on exactly whats supposed to happen. You type some text into a cell and its searching a list of names. When it finds a match what happens? Your simply wanting to select the cell that matches the inputted text? I cannot see a custom macro would have any advantage over the current find method inherent within Excel. | Parry (5696) | ||
| 320003 | 2005-02-01 01:04:00 | I think I know what you mean because I would find that handy as well sometimes. I have to type names and addresses, etc into Excel and if there is already an entry for that person on the worksheet Excel will just autofill the name/address for me. It would be good if it jumped to that cell instead of autofilling. Is that what you mean? At the moment it is quicker and easier to just go Ctrl+F, type in the name then hit Enter. |
FoxyMX (5) | ||
| 320004 | 2005-02-01 01:34:00 | Hi Foxy, are you talking about AutoComplete - this option is turned on via Tools|Options|Edit tab|Enable AutoComplete for cell values. Unfortunately this only works when the list is directly above the cell your typing in rather than anywhere within the sheet. I believe some custom code could achieve this but I would have to give it some thought on the best way to tackle the problem. Im not sure this is what Bobs talking about though. |
Parry (5696) | ||
| 320005 | 2005-02-01 01:58:00 | Ok guys, I’ll try putting my teeth in. If your using any windows programmes click on Help / F1 / index and type a couple of letters. You will notice the “cell” below where your typing tries to follow you. So, instead of looking up the help index we’ll look up someone’s name and address. Is that any clearer? Don’t be scared to say no, I’m not easily offended. :) |
B.M. (505) | ||
| 320006 | 2005-02-01 02:23:00 | No. :p Yup thats an autocomplete feature. So you want the result to go into the cell your typing in thus saving you from filling in the rest. Where is the list located and where is the cell your typing in in relation to this list. |
Parry (5696) | ||
| 320007 | 2005-02-01 03:11:00 | Had a little play with the Auto-complete feature Parry and yep it nearly works. Just got to get the adjacent column to come with it. Lets try this way. In A1 well name the column “Name” and in B1 well “Phone” (Column Names) In A2 well put Parry In A3 John In A4 Fred In A5 Bill In A6 Don Then B2 your phone number 1 and so on down to 5 in B6. OK, now if I type a “B” in A7 I get Bill courtesy of Auto-complete, very good, but how do I get Bills phone number “4” to follow and appear in B7? Can I take my teeth out again? :) |
B.M. (505) | ||
| 320008 | 2005-02-01 03:46:00 | In A1 well name the column “Name” and in B1 well “Phone” (Column Names) Use A2 as your as your auto complete cell In A3 well put Parry In A4 John In A5 Fred In A6 Bill In A7 Don In cell B2 use the lookup formula as follows =LOOKUP(A2,A3:A7,B3:B7) The only thing with this is that you need to keep the names sorted in ascending order (alphabetical). |
Steven (7085) | ||
| 320009 | 2005-02-01 04:20:00 | Hi again, get out the steradent. :D If Stevens formula is not what your meaning (ie you dont want a formula to lookup the value) then perhaps you mean that if you type John again then you dont want to have to type 2 again next to Johns name. The AutoComplete works by each column independantly, so it wont take into consideration that John was entered before and also fill in column B for you. Column B will look at the numbers above and start filling in the number as you type. However, for this situation code can be used to automatically fill in the adjacent column if autocomplete found a match. This may require tweaking but see if this is what you want... 1. Right click the sheet and select View|Code 2. Paste the code below in the right hand window then press Alt+Q to return to the sheet. Private Sub Worksheet_Change(ByVal Target As Range) Dim FindTxt As String, FindRng As Range If Target.Cells.Count > 1 Then Exit Sub FindTxt = Target.AutoComplete(CStr(Target.Text)) If Len(FindTxt) > 0 Then On Error Resume Next Set FindRng = Range(Cells(1, Target.Column), Cells(65536, Target.Column)).Find _ (what:=Target.Text, LookIn:=xlValues).Offset(0, 1) If Not FindRng Is Nothing Then Target.Offset(0, 1).Value = FindRng.Value On Error GoTo 0 End If End Sub |
Parry (5696) | ||
| 320010 | 2005-02-01 04:35:00 | All fixed, piece of cake! :) :) Steven understands gibberish perfectly! :p But I'll try you handiwork Parry just to see if I can get it to go. :confused: Thanks a lot guy's Bob |
B.M. (505) | ||
| 1 2 | |||||