Drop down list with explanations

gord9b

Board Regular
Joined
Jun 12, 2002
Messages
249
On worksheet1 I have a list of codes in named range A1 to A6. In column B1 to b6 I have a description of each of these codes. On worksheet 2, When the user selects cell E2, I want a drop down list pointing to both columns A and B to display the codes and the explanations. However, when the code is selected, I want ONLY the code to appear in the cell E2. Because cell E2 is narrow, only wide enough for the 2 digit code, How can I display the explanation along with the code? How can I pick up only the 2 digit code for insertion into the cell? Can this be done?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Hi Gordon,

Yes, this can be done, but is a bit tricky. First, you can accommodate the code and explanation using a multiple-column dropdown which returns the only the code value from the first column into the destination cell. Second, since the dropdown must fit in a cell too small to display the two columns you must use the GotFocus and LostFocus events of the control to resize the control width up and down--up when the control gets focus and back down when it loses the focus--so that both columns will display, but only when the control drops down.

You must use the ActiveX dropdown combobox control (the one from the Controls toolbox) rather than the Forms dropdown (the one from the Forms toolbar) in order to get the functionality that is necessary.

I hope this gets you going.

Damon
 
Upvote 0
Gordon,

Why not use VLOOKUP along with Data Validation. This would only show the explanation in the Valdation list and the VLOOKUP with return the two digit code. Unless there's a reason why the user must see both explanation and code this would be an easier set up.
 
Upvote 0
The first one I cannot use because I have hundreds of records all requiring this information and I cannot create the combo box for every record. The second one does not work as I keep getting error messages saying I cannot reference another worksheet in the validation box, or must reference a single column in the list option. I think I will just display the descriptions in a pop-up messge box when the cell is selected.
 
Upvote 0
Gordon,

Just name your data validation list. Go to Data/Validation/select list and in the source box enter =listname (what you named your range)
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top