excel下拉列表联动_动态Excel下拉列表

On Monday, AlexJ showed us how to create a short or long drop down list in Excel. With his technique, users can see just the top customers, or all customers. That technique didn't require macros -- it was driven by a formula in the data validation source.

周一，AlexJ向我们展示了如何在Excel中创建简短或长期下拉列表 。 使用他的技术，用户可以只看到主要客户，也可以看到所有客户。 该技术不需要宏，它是由数据验证源中的公式驱动的。

Today, Alex shares an automated version of the short or long data validation list technique. Here's his description of how it works. You can download the zipped Dynamic Data Validation Sample File from the Contextures website. The file contains macros, so enable them to use the dynamic drop down list.

今天，Alex分享了短数据验证列表技术或长数据验证列表技术的自动化版本。 这是他对其工作方式的描述。 您可以从Contextures网站下载压缩的动态数据验证样本文件 。 该文件包含宏，因此使它们能够使用动态下拉列表。

动态数据验证列表 (Dynamic Data Validation Lists)

For an Excel utility running at our office, users are required to enter a project number using a drop down list. There are thousands of these records in the data set, selecting from hundreds of project numbers. This means that the drop-down list is long, and therefore not very useful.

对于在我们办公室运行的Excel实用程序，要求用户使用下拉列表输入项目编号。 数据集中有成千上万的此类记录，可以从数百个项目编号中进行选择。 这意味着下拉列表很长，因此不是很有用。

To address this, we determined that the user would usually select from a short list of active projects, but would also need to select from a long list of all projects or old projects.

为了解决这个问题，我们确定用户通常会从活动项目的简短列表中进行选择，但也需要从所有项目或旧项目的长长列表中进行选择。

There are a number of techniques using dependent data validation in Excel, but these usually require two selection boxes, we wanted to do this with only a single drop down selection. The technique presented allows the user to select from a default list of entries, or select a different list.

在Excel中有很多使用依赖数据验证的技术，但是这些技术通常需要两个选择框，我们希望仅使用一个下拉选择框就可以做到这一点。 呈现的技术允许用户从默认条目列表中进行选择，或选择其他列表。

这个怎么运作 (How It Works)

The two lists are named -- rng.DD1 for the new projects, and rng.DD2 for the full project list. The first cell in each list is a formula, that refers to the other list.

这两个列表的名称分别为 -新项目rng.DD1和完整项目列表rng.DD2。 每个列表中的第一个单元格是一个公式，它引用另一个列表。

=">> GOTO " & $J$3

=“ >>转到”＆$ J $ 3

The cell with the drop down list is named rng.DD_Select.

带有下拉列表的单元格名为rng.DD_Select。

The result cell, $E$5, calculates which list has been selected:

结果单元格$ E $ 5计算选择了哪个列表：

="rng.DD"&IF(rng.DD_Select=$J$3,2,1)

=“ rng.DD”＆IF(rng.DD_Select = $ J $ 3,2,1)

If the selected item matches the heading in cell J3, the result is rng.DD2, otherwise, the result is rng.DD1.

如果所选项目与单元格J3中的标题匹配，则结果为rng.DD2，否则，结果为rng.DD1。

数据验证 (The Data Validation)

The data entry cell has data validation configured for a list, and the following formula that refers to the result cell:

数据输入单元格具有为列表配置的数据验证，以及以下引用结果单元格的公式：

=INDIRECT($E$5)

=直接($ E $ 5)

If the result in cell $E$5 is rng.DD1, the new project list is shown.

如果单元格$ E $ 5中的结果为rng.DD1，则显示新项目列表。

程式设计 (The Programming)

The data validation doesn't require programming, but there is a small VBA routine triggered by the Change Event in cell B5. It tidies up the data entry cell, after a selection is made.

数据验证不需要编程，但是单元格B5中的Change Event触发了一个小的VBA例程。 选择后，它将整理数据输入单元。

This routine will:

该例程将：

1. Clear any entries from the list where the user has selected "------–", or a list header like "----- xxxx ------–"
   从列表中清除用户已选择“ ------–”或列表标题(如“ ----- xxxx ------–”)的所有条目
2. Convert a selection like ">>>> GOTO NEW PROJECT LIST" to "NEW PROJECT LIST"
   将“ >>>> GOTO NEW PROJECT LIST”之类的选项转换为“ NEW PROJECT LIST”

Here is the event code from the data entry sheet.

这是数据输入表中的事件代码。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim str As String
Dim strNew As String
Const strMatch As String = ">> GOTO "
If Target.Address = Me.Range("rng.DD_Select").Address Then
  str = Target.Value
  If str Like "-*" Then
    Target.ClearContents
  Else
    If str Like strMatch & "*" Then
      strNew = Right(str, Len(str) - Len(strMatch))
      Target.Value = strNew
    End If
  End If
End If
End Sub