Dropdown list where user chooses a value, but only a code is entered in the cell

[BarefootPaul]

I want a drop down list where the user can choose from a list of descriptions, but then have a number code for that description be what is retained in the cell. How do I do that?

Thanks

 

[Excelestial]

No quick and easy way to write you instructions to create a drop-down list...it can be done but will need much explanation to help you understand what you're doing. Once your drop-down is created, you'll want a VLOOKUP to look at what's been selected and return the code that is assigned to that drop-down item. It is very simple...but what do you mean by "retained in the cell?" Retained for how long?

 

[BarefootPaul]

Here is an example of what I want. I have two columns in a named range (namePlace) with the first being a billing code and the second is the description of that code.

Code Place
03 School
05 Office
12 Home

I want the user to be able to choose the code by looking at the Place, but have the Code be what is then in the cell. This way, when the billing department sees it, they recognize it and it takes up less space on the worksheet. Some of the Place descriptions are quite long. Is that clear?

I have thought about a userform popping up when the user clicks on that cell and have a combobox that shows both columns but the first column is the bound one. If I can avoid this, I would like to.

I know I could do it with VLookup, but that would take two cells, wouldn't it?

Thanks

 

[Excelestial]

Inbox me...I will put together a sample for you and email back to you this evening. You don't have to use a formula...you can use VBA if you prefer to program and understand the language. Hardwiring it (VBA), though, has its share of problems...doesn't leave you with much wiggle room. Thanks.

 

[BarefootPaul]

I sent you a PM, but forgot to mention that I am okay with it being VBA. I am learning a bit about its power, though that means stumbling through how to do things by asking questions and trying things out. So VBA or formulas is fine.

Thanks again.

 

[West Man]

It can be done without VBA if your codes are numbers. Create a list of the numbers only. Then for each cell in the list use format/cells/custom and in the custom number format box type "Your Description". After doing this, your list will appear to have the description only, when in fact the data is the code only. You can now use data/validation/list for data validation. When selecting from the list via the cell dropdown, only the code will be entered into the cell.

 

[T. Valko]

I want a drop down list where the user can choose from a list of descriptions, but then have a number code for that description be what is retained in the cell. How do I do that?

Thanks

There is a sample file here that does what you want. It requires VBA.

http://contextures.com/excelfiles.html#DV0004

DV0004 - Data Validation Change -- Select a Product from the Data Validation list; an event procedure changes the product name to a product code.

 

[BarefootPaul]

Thanks everyone. I am going to see if I can adapt the code from the contextures site to my needs. I'll be sure to let you know if I run into troubles.

I really appreciate this site as I learn how to do things that I want to do, but don't know how.

 

[T. Valko]

Thanks everyone. I am going to see if I can adapt the code from the contextures site to my needs. I'll be sure to let you know if I run into troubles.

I really appreciate this site as I learn how to do things that I want to do, but don't know how.

Good deal. Thanks for the feedback!

 

[BarefootPaul]

After far more time than it should have taken me, I have it working now. However, I have one more question about it. How do I make the dropdown list wider than the column? The column is really narrow since it only needs to have a 2 digit number stored in it, but the description that is used in the data validation match is much longer and the user won't be able to distinguish some of them if they can't read more than the column width allows.

I can't make the colomn wider because there is not room to do that and keep it all on a single printed page. There are actually 6 different columns that this applies to.

Thanks.