Displaying two columns in Data Validation List

[FarzanaSaleem]

In an Excel File, there are two sheets; Sheet1 and Sheet2.

Sheet1 has two Columns:
Column A = JobCode
Column B = Description

JobCode is a named range and refers to =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
Description is a named range and refers to =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,2)

Sheet2 is as follows:
A1 = JobCode
A2 = Data -> Validation -> List (Source = JobCode)

B1 = Description
B2 = =IF(ISNA(VLOOKUP(A2,Description,2,FALSE)),"",VLOOKUP(A2,Description,2,FALSE))

Now when any JobCode is selected in Sheet2->Column A, its corresponding description is shown in Sheet2->Column B.

What is needed is to show both JobCode and Description Columns in JobCode Combo and when the user selects any one, JobCode is to be stored in JobCode Column.

Let's say, entries are as follows:

Sheet1:

A1 = JobCode
A2 = MGR
A3 = ACT
A4 = PROG

B1 = Description
B2 = Manager
B3 = Accountant
B4 = Programmer

Sheet2, JobCode Combo box should show both JobCode and Description like:
MGR Manager
ACT Accountatn
PROG Programmer

When user selects first row, then MGR should be stored in the cell.

I tried to change the Data->Validation->List->Source from JobTitle to Description but got the following error message:

The List Source must be a delimited list, or a reference to single row or column

 

[xlbo]

Sorry - can't do that in a Data>Validation list
AFAIK, you can only do this in a "Controls Toolbox" combobox or listbox

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[FarzanaSaleem]

Geoff,

Please explain to me how to do this in a "Controls Toolbox" combobox or listbox.

There is an option View -> Toolbars -> Control Toolbox and then Combobox or listbox can be picked from the toolbar but how to link it with a particular cell and how to populate it.

 

[xlbo]

Have a look at the help file 1st

type "combobox" into the answer wizard and have a look at the properties of the combobox - there is a columncount property you will need to set amongst other things. If you have problems with a particular bit of the set up, post back and we'll see what we can do....

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[FarzanaSaleem]

I proceeded as follows:

1. View -> Toolbars -> Control Toolbox
2. Dragged Combobox Control on Sheet2
3. Wrote the following in Sheet2(Code)

Private Sub ComboBox1_Click()
With ComboBox1
.TopLeftCell.Value = .Value
.Visible = False
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
If .Row > 1 And .Column = 1 Then
ComboBox1.Top = .Top
ComboBox1.Left = .Left
ComboBox1.Width = .Width
ComboBox1.Height = .Height
ComboBox1.Visible = True
Else
ComboBox1.Visible = False
End If
End With
End Sub

4. Wrote the following in ThisWorkbook(Code)

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Sheet2.ComboBox1.Clear
Sheet2.ComboBox1.BoundColumn = 1
Sheet2.ComboBox1.ColumnCount = 2
Sheet2.ComboBox1.ListFillRange = Description
End Sub

When I click any cell in JobCode Column, Combobox appears but it does not contain JobCode and Description values. It is shown as blank.

 

[xlbo]

Surely that is because you are clearing the combo when the window is activated. I would've thought you need to set the data to appear in the combobox in the SelectionChange event - use either the rowsource property or the Additem method to populate the combo

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[FarzanaSaleem]

Thanks Geoff but I did not find rowsource property in Controls Toolbox Combobox control.

I have emailed the excel file to you.

If you could please review it and indicate my mistake


Thanks

 

[xlbo]

Farzana - if I wanted you to email me the file, I would've posted my email address for you. I didn't & I am not best pleased that you have dug it out from some other thread and used it. This is NOT a helpdesk and as such no-one is under ANY obligation to help anyone else out. We do so because we like helping and sharing knowledge - you are abusing that offer.

There is no rowsource property as you are using the combobox on the worksheet itself - you must therefore use the "Listfillrange" or the Additem METHOD.

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[FarzanaSaleem]

Thanks Geoff and sorry for the email.

I should not have done that.

My apology again


Regards

 

[xlbo]

No probs - the general rule is if someone OFFERS to look at a file off-line because there is a sticking point and they need to see the layout / data, that is fine but unless someone offers, you should not email.

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[klmorgan]

Not sure if I understand what you want but you can set any number of levels validation:

Set a Validation in Say A2 that uses a list having:

Model 1
Model 2
Model 3

Then have a list named Model 1 that holds parts for model 1

ie. Brake Pad
Starter
Disc

and likewise for the other models

then in say cell d2 set validation source as =INDIRECT(A2)

This will then set the choices in D2 conditional on the choice made in a2

You can then cascade this down to further levels if you want

Regards

Keith

P.S. if this is not clear I will email you an example

 

[xlbo]

Keith - this is about having a multi COLUMN dropdown NOT having LINKED dropdowns

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question