How can I implement a multi-column list for data validation in a worksheet,
i.e., not in user form?

I have a worksheet that user needs to pick a vendor from a valid list. I
want to show both the vendor number and name on the list, but, I prefer not
to use user form to accomplish this.

In addition, the validation list does not seem to be as user-friendly as it
is in Access, which will jump to the right record as you type. Is there
something in Excel that is similar? Your help is appreciated.

Maybe you can combine the columns in another column and use that as your
data|validation list:

=A1&"--"&b1
copied down.

Data|Validation doesn't support this autocompletion stuff. But you could use a
combobox from the Control toolbox toolbar (still on the worksheet) that would
support this.

Fractal wrote:

How can I implement a multi-column list for data validation in a worksheet,
i.e., not in user form?

I have a worksheet that user needs to pick a vendor from a valid list. I
want to show both the vendor number and name on the list, but, I prefer not
to use user form to accomplish this.

In addition, the validation list does not seem to be as user-friendly as it
is in Access, which will jump to the right record as you type. Is there
something in Excel that is similar? Your help is appreciated.

--

Dave Peterson

You can use an ActiveX combo box on the worksheet. Click VIEW in the menu
and select TOOL BARS and open the CONTROL TOOLBOX.

This will look very much like the Access tool box in forms and reports.

You can create your vendor list and name the range to make it easier on
yourself.

After inserting your control, and if you do an Alt + Click & Drag when
inserting it the combo box will size according to the cell borders, right
click on it and select PROPERTIES.

Use the ListFillRange property to state the range your look up table occupies

Use the LinkedCell property to indicate what cell in the workbook you want
to populate after a selection has been made.

Turn design mode off by clicking the Design button on the Control Toolbox
toolbar

Use the BoundColumn property to indicate which column in the lookup you want
returned

User the ColumnCount property to indicate how many columns you want
displayed when the combo is dropped down.



--
Kevin Backmann


"Fractal" wrote:

How can I implement a multi-column list for data validation in a worksheet,
i.e., not in user form?

I have a worksheet that user needs to pick a vendor from a valid list. I
want to show both the vendor number and name on the list, but, I prefer not
to use user form to accomplish this.

In addition, the validation list does not seem to be as user-friendly as it
is in Access, which will jump to the right record as you type. Is there
something in Excel that is similar? Your help is appreciated.

Thank you very much, it works the way I like.

How do I populate it so that the whole column have this combox? I created a
named range for my lookup table, then I added a combox on E2, for its
property "LinkFillRange", I entered the named range. For "LinkedCell", I
enter F2. BoundColumn and ColumnCount both are 2. I was able to choose a
vendor number from the dropdown list and the combox fill the name. I am
trying to copy down this combox and do not know how to.

"Kevin B" wrote:

You can use an ActiveX combo box on the worksheet. Click VIEW in the menu
and select TOOL BARS and open the CONTROL TOOLBOX.

This will look very much like the Access tool box in forms and reports.

You can create your vendor list and name the range to make it easier on
yourself.

After inserting your control, and if you do an Alt + Click & Drag when
inserting it the combo box will size according to the cell borders, right
click on it and select PROPERTIES.

Use the ListFillRange property to state the range your look up table occupies

Use the LinkedCell property to indicate what cell in the workbook you want
to populate after a selection has been made.

Turn design mode off by clicking the Design button on the Control Toolbox
toolbar

Use the BoundColumn property to indicate which column in the lookup you want
returned

User the ColumnCount property to indicate how many columns you want
displayed when the combo is dropped down.



--
Kevin Backmann


"Fractal" wrote:

How can I implement a multi-column list for data validation in a worksheet,
i.e., not in user form?

I have a worksheet that user needs to pick a vendor from a valid list. I
want to show both the vendor number and name on the list, but, I prefer not
to use user form to accomplish this.

In addition, the validation list does not seem to be as user-friendly as it
is in Access, which will jump to the right record as you type. Is there
something in Excel that is similar? Your help is appreciated.