excel创建下拉列表多选_使用Excel下拉列表创建世界和平

excel创建下拉列表多选

Can the sales staff and accounting staff ever work in peace? One group wants to see product descriptions, when entering orders. The other group thinks the descriptions clutter up the worksheet -- they just want the product codes.

销售人员和会计人员可以和平共处吗？ 输入订单时，一组希望查看产品说明。 另一组成员认为这些描述使工作表混乱—他们只想要产品代码。

Try this data validation trick, and you might be nominated for next year's Nobel Peace Prize. (Results not guaranteed.)

试试这个数据验证技巧，您可能会获得明年的诺贝尔和平奖提名。 (结果无法保证。)

创建下拉列表 (Create Drop Down Lists)

To make it easier for users to enter data in an Excel workbook, you can create drop down lists in the cells, by using Excel data validation.

为了使用户更容易在Excel工作簿中输入数据，您可以使用Excel 数据验证在单元格中创建下拉列表。

In this example the product list is in an Excel Table, and the ProductShow column is a named range -- ProdList. The ProdList range is used as the source for the drop down lists on the order entry sheet.

在此示例中，产品列表在Excel Table中 ，而ProductShow列是命名范围 -ProdList。 ProdList范围用作订单输入表上下拉列表的来源。

加魔法 (Add the Magic)

After the product is selected from the drop down list, the full description is automatically replaced by the product code. How does it happen? It's the magic of Excel VBA -- event code that runs when the worksheet is changed.

从下拉列表中选择产品后，完整说明将自动替换为产品代码。 它是怎么发生的？ 这是Excel VBA的魔力-更改工作表时运行的事件代码。

The Excel VBA code uses the Match worksheet function to find the row number in the lookup list. It replaces the selected product description with the matching Product Code from that row in the lookup list.

Excel VBA代码使用“匹配工作表”功能在查找列表中查找行号。 它用查找列表中该行的匹配产品代码替换所选产品描述。

Peace at last! Your co-workers will be happy that they don't have to memorize the product codes, and the accounting department will be grateful that they get the data in the format they need.

终于和平了！ 您的同事将很高兴不必记住产品代码，会计部门将很感激他们以所需的格式获取数据。

下载样本文件 (Download the Sample File)

To see the Excel VBA code that changes the product name to a product code, go to the Contextures website, and download the sample file: DV004: Data Validation Change.

若要查看将产品名称更改为产品代码的Excel VBA代码，请访问Contextures网站，然后下载示例文件： DV004：数据验证更改 。

The example used here is the Excel 2007 version, and there is also an Excel 2003 version of the sample file.

此处使用的示例是Excel 2007版本，并且示例文件也有Excel 2003版本。

观看数据验证视频 (Watch the Data Validation Video)

You can watch this video to see the steps for creating an Excel Table, naming a column in that table, then using that name when creating the data validation drop down list.

您可以观看此视频，以查看创建Excel表，在该表中命名一列，然后在创建数据验证下拉列表时使用该名称的步骤。

演示地址

翻译自: https://contexturesblog.com/archives/2010/11/03/create-world-peace-with-an-excel-drop-down-list/

excel创建下拉列表多选