+ Reply to Thread
Results 1 to 10 of 10

Create a Drop down that will list all names starting with initial letter of names.

  1. #1
    Registered User
    Join Date
    01-13-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    5

    Create a Drop down that will list all names starting with initial letter of names.

    I have a long customer listing, names 5 to 36 characters and several with their location in the name as well as a few common duplicate names. I am trying to produce sheet where the customers name once selected opens in the customers spreadsheet and data can be added/amended for sales etc.The lists are not in alphabetical order as when created a customer number is automatically allocated. My aim is just to type in the first letter of the name and the dropdown appears the customer is selected and their card appears.
    I have tried data validation, lookup, vlookup, Dropdown and Match/find. they only return the first record found and no sign of any others. Find returned all instances of the letter appearing in every name. Can anyone advise which way next to try or am I on to a hiding to nothing. Thanks for any comments and help.
    Mike.
    Last edited by blue leader; 01-16-2014 at 03:57 PM.

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Create a Drop down that will list all names starting with initial letter of names.

    Hey Mike,

    Can you post a sample data set?
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Registered User
    Join Date
    01-13-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Create a Drop down that will list all names starting with initial letter of names.

    Hi,
    I am not sure what a data set is but this is the sheet that I want the dropdown type box to work in. The customer list is over 120 names and as new names are added on to the end they are not in alphabetical order. Some of the customers have several premises, all billed/invoiced separatly, and only the location identifies them i.e. Sam Smith Tadcaster, Sam Smith Oteley. At this time there are no more than five locations/similarities with any customer. If more info needed please ask.
    Please Login or Register  to view this content.
    I cannot work out how to attach the part of the spreadsheet required. can someone help?

  4. #4
    Registered User
    Join Date
    01-13-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Create a Drop down that will list all names starting with initial letter of names.

    No Paperclip on first reply box so this should work.[CODE][Sales Sheet 2013.xlsx/CODE]

  5. #5
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Create a Drop down that will list all names starting with initial letter of names.

    So your file doesnt show where the list of names are.

    So here is what I have done in the past. Create a dynamic named range around the list of names, then create a macro to automatically sort them whenever the sheet is touched, or link the dynamic named range to a pivot table, and have it updated whenever the row count grows for the list.

    It is really pretty simple.

    Where do the names come from? Do you want the file itself to aggregate them or are you or someone manually typing them somewhere?

  6. #6
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Create a Drop down that will list all names starting with initial letter of names.

    So this blog does a great job explaining the dynamic named range:
    http://blog.contextures.com/archives...automatically/

    I implemented a possible solution in the attached workbook:
    I used fake names (actually I downloaded 100 favorite baby names or something LOL)

    Sales Sheet 2013(Updated).xlsx

    I have included a tab called "lists" and I have created two names ranges:
    lstNames and PivotNames

    Let me know if that will work for you.
    Also you can implement some simple automation features like a row count and if that row count changes you can fire off a macro to automatically update the pivot table with the list of names. Simple stuff but used together make for an automated tool.

  7. #7
    Registered User
    Join Date
    01-13-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Create a Drop down that will list all names starting with initial letter of names.

    Hi,
    Thanks for your reply. The names are drawn from another sheet -Customer details - but could be listed on this sheet,off piste as it were. This is an accounts package I am putting together for my sons one man band business. This is the sales sheet and together with an expenses sheet, bank account sheet and a customer details sheet is linked to a profit/loss/balance sheet to make my life easier as his accountant /tax form filler. I have a day elsewhere today and will check out your suggestion thursday pm and will let you know how it works in with my "maset plan".
    Regards Mike.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Create a Drop down that will list all names starting with initial letter of names.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  9. #9
    Registered User
    Join Date
    01-13-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Create a Drop down that will list all names starting with initial letter of names.

    Mike,
    Thanks very much for your help. This has opened up new avenues of approach to create working accounts system. I have a lot of reading to do and to understand how things relate to one another. I am going to modify my workbooks and hope I can succeed without too many requests for help. At least I now know I can get help here. Once again, many thanks.
    Mike.

  10. #10
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Create a Drop down that will list all names starting with initial letter of names.

    Great, glad I could help. If you have any other specific questions just post it up on the forum.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] have list of names and want to switch names around by putting the first initial last
    By ctc75 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-10-2014, 01:09 PM
  2. [SOLVED] Number of times a letter occurs in list of names
    By m1llz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-29-2013, 07:39 AM
  3. Replies: 2
    Last Post: 12-20-2013, 04:01 AM
  4. Filter list of names by letter
    By Mark1011 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-19-2012, 12:10 AM
  5. Create drop list on form from range names?
    By Ed in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-15-2005, 12:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1