drop-down list

[Guest]

Is there a way to set up a drop-down list that shows numerous cells, yet only
fills in one cell? (ie...I have a list of banks in one column and the
account numbers in the next column. I'd like to see both when I pull the
list down, but only the account number fill in when selected. Is this
possible?

 

[Guest]

I am running Excel 2003 and it looks like drop down lists only show 1 column.
could you do the application in Access, where you can show more than one
column then use the menu option to export to Excel for further analysis?

 

[Rick Rothstein \(MVP - VB\)]

Is there a way to set up a drop-down list that shows numerous cells, yet

only
fills in one cell? (ie...I have a list of banks in one column and the
account numbers in the next column. I'd like to see both when I pull the
list down, but only the account number fill in when selected. Is this
possible?

You can use a Visual Basic ComboBox for this. If you haven't already done
so, select View/Toolbars/Visual Basic from Excel's menu bar. Click on the
Control Toolbox icon from the Visual Basic tool bar (looks like a wrench and
hammer crossed over each other). This will add the Control Toolbar to the
spreadsheet's tool bars. From the Control Box, click on the ComboBox and
draw it onto your spreadsheet. If you are not already in Design Mode, select
the Design Mode icon from the Visual Basic tool bar (it's to the right of
the Control Toolbox icon). Once that is done, right click the ComboBox and
select Properties from the popup menu. Set the ColumnCount property to 2;
set the BoundColumn property to 2 (second of the two columns); set the
LinkedCell property to the cell you want to display your results in; and set
the ListFillRange to the 2-column range containing you list of bank names in
the first column and the account numbers in the second column. You can play
with the other properties if you want, but that is basically all you have to
do. Now, click the Design Mode icon on the Visual Basic toolbar to turn it
off. Click on the ComboBox and you should have a 2-column listing showing
your banks and account numbers. Select one and watch the cell you linked the
ComboBox to. One other thing, besides properties, that you might want to
play with is the Format Control option available when you right-click the
ComboBox in Design Mode.

Rick

 

[Debra Dalgleish]

You can do this with data validation and programming. There's a sample
file here:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0005 - Data Validation "Columns"'

 

[Guest]

Cool. Thanks a lot.

You can use a Visual Basic ComboBox for this. If you haven't already done
so, select View/Toolbars/Visual Basic from Excel's menu bar. Click on the
Control Toolbox icon from the Visual Basic tool bar (looks like a wrench and
hammer crossed over each other). This will add the Control Toolbar to the
spreadsheet's tool bars. From the Control Box, click on the ComboBox and
draw it onto your spreadsheet. If you are not already in Design Mode, select
the Design Mode icon from the Visual Basic tool bar (it's to the right of
the Control Toolbox icon). Once that is done, right click the ComboBox and
select Properties from the popup menu. Set the ColumnCount property to 2;
set the BoundColumn property to 2 (second of the two columns); set the
LinkedCell property to the cell you want to display your results in; and set
the ListFillRange to the 2-column range containing you list of bank names in
the first column and the account numbers in the second column. You can play
with the other properties if you want, but that is basically all you have to
do. Now, click the Design Mode icon on the Visual Basic toolbar to turn it
off. Click on the ComboBox and you should have a 2-column listing showing
your banks and account numbers. Select one and watch the cell you linked the
ComboBox to. One other thing, besides properties, that you might want to
play with is the Format Control option available when you right-click the
ComboBox in Design Mode.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Oh, and I forgot to mention... because it is a Visual Basic "thing", it is
fully programmable via macros. When in Design Mode, double click the
ComboBox and you will be taken into the VBA environment where you can make
use of the events exposed for this control.

Rick