Adding to dropdown list by typing (Excel 2007)

Hello,

I am using Excel 2007 and I have a dropdown menu that autopopulates a field next to it. So, I can select someone's name from the menu and then their phone number appears in the cell next to it. I would like to have it so if the name isn't in the list, the user can type it into the blank, and then type the phone number into the next cell (which usually autopopulates). Then the new name would be part of the dropdown menu, and the correct phone number will autopopulate. I want to do this to make it as easy as possible for the people using the form to add names/numbers if needed. Is this possible? If not, do you have any other suggestions? The people using the file don't want to have to edit anything and need it to be as simple as possible to add new things.

I'm pretty comfortable with excel basics, but the intermediate/advanced stuff is still fairly new to me!

Thanks!

Try this,

You may be able to make it fit what you would like, if you cannot, i will help if you attach your workbook.

Let me know if this will work

Thanks a lot! I'm trying to make a userform now (my first time), but unfortunately I'm a little dumb at this stuff right now. The list I want to add to is in a worksheet called "Input Sheet" and the name list is G6 to G16, and the phone number list is H6 to H16. The first time the userform is used to add data I want the value to be added at the end of this list (G17 and H17). All I can find is how to have it populate the next blank row in a worksheet and I'm not sure how to edit it to work for me. I know I could just add a new worksheet, but I would rather not if I can avoid it. I followed most of these steps: http://www.contextures.com/xlUserForm01.html

and I named the text boxes accname and accnum. Sorry if I'm not even close to being on the right track!

Edit: I just saw what amotto11 posted, going to take a look now. Thanks again!

the only thing you may have to change is the vlookup formula in the phone number, and the code that tells what sheet to look for in the userform since your sheet name is not sheet1. As long as there is nothing else on this page this should work because it looks for the last row that is empty to place the new name and number, so if there is data below in any column then the code will not work as you would like and your list will have a lot of blank spaces in it. you can adapt for this, if you just change the first line in my code to only look in the column that you would like to add the value too.

Ok, I got it so it adds the data to the correct columns, but it's adding it way further down the list than it should be. It is adding it to line 50 instead of line 17. There is no data in line 17 on, so I'm not sure what's happening. Here is the code I'm using (only slightly edited, I changed the name of my text boxes instead of changing the code you gave me):

Please Login or Register  to view this content.

Ah of course. I have other columns in my "Input Sheet", so it is putting the new data at the bottom of that, leaving blanks. I suppose I could rework the data so each thing is on a separate sheet. You mentioned that I could get around this by changing ther first line.. I'm pretty new at this, so how should I change it?

Thanks! It worked just like it's supposed to, I just have one problem that I didn't anticipate. When I select a name, the number that populates isn't correct. I believe because the list isn't in alphabetical order. Is there a way to have the list automatically sort, or a way I can make the dropdown menu read the list how I want? I'm also a bit confused on how to make the menu ignore the blanks at the bottom of the list (or have it just adjust the range so blanks aren't included... Whatever is easier). Thanks for all the help, and sorry for being a pain. I did look up how to do this, but was a little confused by what I was finding.

For your first question. It shouldn't matter if the list is in alphabetical form or not. It is just a vlookup that is looking up the value. Do you have two names that are the same. The vlookup would only look at the first value. Otherwise there shouldn't be a problem with the correct number populating. Could you give me an example or something more that i could have to see what your problem is. I will try to fix the problem of blanks in the dropdown.

---------- Post added at 09:02 AM ---------- Previous post was at 08:40 AM ----------

KJL,

Is the only time that the list gets larger when the person imputs a new name? I may have a solution if that is the case. you can let them enter a name in then make the list get longer. This would be the new code with RoyUK's solution in it. the end of this code will build the data validation box and populate it. when you add new names it will change the length of the data validation, but only when you add new names through the button. If you were going to start just by placing names on the page, all you would have to do is make your last name and number with the button and it will make your data validation and set it good to go. Remember my code is not using your cells, so just change the references and you should be good to go.

Please Login or Register  to view this content.

KJL,

I am sorry, but i really can't make heads or tails of what you are saying is the problem.

I see that you are using my form to create new lodging, and i am using your dropdowns on the first page and i see that the phone numbers are coming in correctly. Could you give me more of an understanding of what you are doing and what the problem is. Thanks, and sorry i was not able to understand your last post very well.

Perfect, I go everything working exactly how I wanted. Thanks for all of your help, I learned a lot from this!