PDA

View Full Version : "XL brainteaser"



B.M.
01-02-2005, 01:51 PM
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

Parry
01-02-2005, 01:57 PM
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.

FoxyMX
01-02-2005, 02:04 PM
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.

Parry
01-02-2005, 02:34 PM
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.

B.M.
01-02-2005, 02:58 PM
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. :)

Parry
01-02-2005, 03:23 PM
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.

B.M.
01-02-2005, 04:11 PM
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? :)

Steven
01-02-2005, 04:46 PM
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).

Parry
01-02-2005, 05:20 PM
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

B.M.
01-02-2005, 05:35 PM
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

FoxyMX
02-02-2005, 12:16 PM
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.
Hi Parry,

Yes, I am talking about AutoComplete and it works fine for me, most of the time. The list is directly above the cell I am typing in and apart from when the entry is waaaaay above where I am typing it normally works.

What I was musing over, and don't think it would be possible to easily do, if at all, is instead of the AutoComplete filling in the cell for me it would jump to that cell that has the entry I am about to type in. As it is now, when it AutoCompletes an entry it indicates that somewhere above there is already an entry for that person's name/address or whatever and I then do a Ctrl+F to find it in order to add more information in another cell associated with that entry. Does that make sense to you?

It's not a big deal and if it didn't have a quick and simple solution I wouldn't bother but I have discovered lots of neat tricks that Excel can do from reading yours and others replies to questions here that I never dreamed it can do so if I don't ask I'll never know. :D

Parry
02-02-2005, 12:44 PM
Hi FoxyMX, I was thinking you were meaning using AutoComplete when your not actually in the same column as the list of values. Thats damn hard to figure out as theres no events to monitor while your typing in a cell, only when youve finished typing in the cell.

However, selecting a cell that matches is pretty easy with code. One question though, are the cells above unique? If not, how do you decide which cell to warp to?

Heres some code with the following assumptions:-
1. You only want to look above the current cell
2. You want to clear the contents of the cell if there is an autocomplete match
3. If theres a match select the first cell (from top to bottom) that matches the value entered.

This is using a Worksheet_Change event that runs after you press enter when entering a value into a cell. To place the code do the following:=
1. Right click the sheet tab and select View Code
2. Paste code in right hand window and Press Alt+Q to Exit the Visual Basic Editor


Private Sub Worksheet_Change(ByVal Target As Range)
Dim StrVal As String

'Exit procedure if more than one cell changes
If Target.Cells.Count > 1 Then Exit Sub

'Exit procedure if cell is in row 1
If Target.Cells.Row = 1 Then Exit Sub

'Set a string variable returning autocomplete value
StrVal = Target.AutoComplete(Target.Text)

'if string variable length > 0 then theres a match
If Len(StrVal) > 0 And StrVal <> "" Then
'Select the matching cell
Range(Cells(1, Target.Column), Target).Find( _
What:=Target.Text, After:=Target, LookIn:=xlValues).Select

'Clear contents of the cell that was just changed
Target.ClearContents
End If

End Sub

EDIT: Damn I stuffed up the code. Ive updated the code above so hopefully this will work properly.

FoxyMX
03-02-2005, 08:36 AM
Hi Parry,

Many thanks for taking the time to offer a solution to my query but I think it is going to be more trouble than it is worth. My worksheet is not set up and used as you assumed so I believe that it would probably be quite complicated to do what I was thinking.

Never mind, I have another query that you are most likely to be able to solve and will start another thread on it if an idea that I have does not work out. :p

Parry
03-02-2005, 10:21 AM
Alot can be achieved with code. For instance after moving to the cell above it could go along the row and select the next empty column etc. However, you need to be exact about what you require and how this is applied in different situations. Whatever logic you use yourself to determine where to enter the data may be able to be applied using code, depending upon how clear the steps are. ;)