Contact list navigation

[lavaghman]

Hi there,
I have a contacts list which is becoming quite large and I want to tidy it up and make it easier to navigate. The list will be used by a number of people who are on phones and who do not need the ability to edit the list.

Ideally I would like to be able to strike a letter for example 'M' and be taken to the location in the excel sheet where all items beginning with 'M' start. It would also be great if I could type a 2nd letter for example 'I' and this would show me all entries beginning with 'MI'.

I have been experimenting with the auto filter function but this causes all data outside the search letter to disappear whereas I just want to go to the section where it is and not have all other data disappear.

Is this possible? I am happy to look this up myself if someone can give me the basic idea! Also would appreciate any other suggestions as how I might make a large contact list more navigable and make it easier to find the data as required.

Thanks for your time and help,

kind regards,
John

[SteveG]

John,

You could use a combo box for your phone reps to lookup the contact name and then this formula in the cells to the right of it to populate the corresponding contact info like Phone, Company etc...

=OFFSET(INDEX($A$5:$A$15,MATCH($A$4,$A$5:$A$15,0)),,1)

This will not take them to the point in the list where your contact info resides but rather pull that info automatically and populate in the same cells every time. You need to link the combo box to A4 in my example. I usually then put the combo box over it so you don't see the value twice. Then format the combo box as MatchEntryComplete. This will enable users to enter in the first letter or multiple letters to locate the contact from a drop down list.

In this example, I put Contact in A5:A15, Phone # in B5:B15 and Company Name in C5:C15. My combo box covers cell A4. In B3 & C3 I put my headers, Phone # & Company Name. In B4 use the above and in C4

=OFFSET(INDEX($A$5:$A$15,MATCH($A$4,$A$5:$A$15,0)),,2)

I changed the 1 at the end of the formula to a 2 in order to get the 2nd column of data from the contact name match.

Does that help?

Steve