2 column drop down box

[Trazzel]

How do you create a 2-column drop down box where the box populates from column 2 based on the data chosen from column 1?
For instance, the first column is a list of departments and the second column is a list of the numerical codes assigned to the departments.
In the spreadsheet, if the MARKETING department is chosen, the assigned code 4895 would appear in the box.

Thank you.

 

[dhookom]

I would use VLookup() to get the code based on the selected department name. If you are using forms, you can specify the bound column and have other functionality.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SkipVought]

Hi,

You don’t use a 2-column dropdown. You have 1 dropdown for the UNIQUE list of values from the first column and then a second dropdown for the subset of values in the second column related to the selected value in the first column.

So you need 2 lists:
List1: unique values from List 2 column 1
List2: two columns sorted by column 1 at a minimum

Dropdown1: Data > Validation LIST
Dropdown2: Data > Validation CUSTOM formula that references the selection from Dropdown1 using the OFFSET() function.

How about posting your 2-column Table/list so we can post a cogent formula.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Guess I misread, thinking that each Department had multiple codes.

So the solution is much simpler. Duane's solution will work.

I made one using Named Ranges. I rather use INDEX() and MATCH() along with Named Ranges,based on labels in adjacent cells...
[tt]
lookup Forumula: =INDEX(DeptID,MATCH(SelectedDept,Dept,0))
[/tt]

I also added Variable Named Ranges based on formulas Defined in Formulas > Defined Names > Name Manager
[tt]
Dept =OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)
DeptID =OFFSET(Sheet1!$A$1,1,1,COUNTA(Sheet1!$A:$A)-1,1)
[/tt]

See attached workbook sample

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

If Skip's solution does not meet your needs, use activex combo box. It is available from "developer" tab in ribbon. You need to enter design mode (icon in the tab), right-click the control and select "properties" (alternative: display VB editor, select design mode, select sheet icon in project explorer and combo box in drop-down in properties window).

See attached file.

Customised combo box properties:
1, ColimnCount, set to 2 (columns displayed),
2. BoundColumn, set to 2 ( column with the returned value),
3. ColumnHeads, set to True (headers displayed in combo box, works only for list linked to worksheet),
4. ColumnWidths, set to 70 and 40 (widths in points),
5. LinkedCell, set to G2 (value returned here),
6. ListFillRange, set to A2:B4 (range taken to list, if list has headers, headers are taken from the row above, here A1:B1).



combo