Drop down list with explanations

gord9b

Board Regular
Joined
Jun 12, 2002
Messages
247
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?
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
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.
 

gord9b

Board Regular
Joined
Jun 12, 2002
Messages
247
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.
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,705
Messages
5,597,668
Members
414,162
Latest member
jborjal1967

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
Top