excel - 根据 vlookup 设置下拉值

Question

我有一个工作表 UserEntry有 2 列，Block和 Address .我想根据另一个工作表验证这两个 Validation具有相同的列名。 Validation上的数据单子(monad)如下:

Block | Address
---------------
001   | 101
001   | 101.3
001A  | 35
020-1 | 203
020-1 | 203.5
020-1 | 204.1

...

大约有 11000 个不同的 block ，以及大约 40000 个 block /地址对。

我的目标是，如果用户在 Block 中输入值 UserEntry 上的列表， Address 中的下拉选项列更改以对应 Block .

我尝试使用此公式的自定义验证:
=VLOOKUP(UserEntry!A2,Validation!A2:B40000)
但这被评估为错误。我在各种论坛中看到了一些解决方案，这些解决方案涉及设置命名范围，然后让 VLOOKUP() 搜索适当的命名范围，但它似乎在这里不起作用，因为我必须创建 11000 个命名范围。

如何使 Address 的验证下拉菜单包括与给定 Block 对应的所有值值(value)？

Answer 1

您没有提到 VBA，但这里有一个使用它的解决方案。

步骤1

创建 block 地址关系的主表。确保这是按 Block 排序的。我使用了Sheet1:



单元格 E2 很重要。您实际上不必在那里放任何东西，但宏会使用它。单元格 E3 仅用于显示，但您将暂时使用公式(此处已将其注释掉，以便您查看)。

第2步

创建一个命名范围。 Refers to: 中的公式就是上面 E3 中看到的，这里可以看到对单元格 E2 的引用。为了您的方便，公式是

=OFFSET($A$1,MATCH($E$2,$A:$A,0)-1,1,COUNTIF($A:$A,$E$2),1)

第 3 步

设置一个新的工作表 (Sheet2)，将在其中进行数据输入。为 Address 列创建数据验证，如图所示。



第4步

打开 VBA 编辑器并将此代码粘贴到 Sheet2 的模块中。如果您愿意，可以删除 Debug 语句。再次注意在 E2 上对单元格 Sheet1 的引用:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Debug.Print "fired on " & ActiveCell.Address
  If Not Application.Intersect(Target, Range("B:B")) Is Nothing Then
    Sheets("Sheet1").Range("E2").Value = ActiveCell.Offset(0, -1).Value
  End If
End Sub

第 5 步

享受。您的数据验证现在是上下文相关的。例子: