基于vlookup设置下拉列表值 [英] set drop-down values based on vlookup

问题描述

我有一个工作表 UserEntry 与2列，阻止和地址。我想根据具有相同列名称的另一个工作表验证来验证这两个。 验证表中的数据如下：

I have a worksheet UserEntry with 2 columns, Block and Address. I want to validate both of these based on another worksheet Validation with the same column names. The data on the Validation sheet is as follows:

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

...

有大约11000个不同的块， 40000个块/地址对。

There are about 11000 different blocks, and about 40000 block/address pairs.

我的目标是，如果用户在$ code> Block 列中输入值 UserEntry 表格中， Address 列中的下拉选项更改为与块对应。

My goal is that if a user enters a value into the Block column on the UserEntry sheet, the drop-down choices in the Address column change to correspond with that Block.

我尝试使用以下公式的自定义验证：

I tried using Custom validation with this formula:

= VLOOKUP（UserEntry！A2，Validation！A2：B40000）

但评估为错误。我在各种论坛中看到了一些解决方案，涉及设置命名范围，然后使用VLOOKUP（）搜索适当的命名范围，但似乎在这里不会工作，因为我必须创建11000个命名范围。

But that evaluated to an error. I saw some solutions in various forums that involved setting named ranges and then having the VLOOKUP() search for the appropriate named range, but it seems like that won't work here because I'd have to create 11000 named ranges.

如何为地址确认下拉列表中包含与给定相对应的所有值阻止值？

How can I make the validation drop-down for Address include all the values corresponding to a given Block value?

推荐答案

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

You didn't mention VBA but here is a solution that uses it.

创建Block-Address关系的主表。确保这是按阻止排序的。我使用Sheet1：

Create a master table of Block-Address relationships. Make sure this is sorted on Block. I used Sheet1:

单元格 E2 很重要。你实际上不必把任何东西放在那里，但宏会使用它。单元格 E3 仅用于显示，但您将使用公式（这里注释掉，以便您可以看到它）。

Cell E2 is important. You don't actually have to put anything there, but the macro will use it. Cell E3 is for show only, but you will use the formula (which is commented out here so you can see it) momentarily.

创建一个命名范围。 中的公式是指：是您在 E3 中看到的内容，您可以看到单元格 E2 这里。您的方便的公式是

Create a named range. The formula in Refers to: is what you saw in E3 above, and you can see the reference to cell E2 here. The formula for your convenience is

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

设置数据输入将发生的新工作表（Sheet2）。为 Address 列创建数据验证，如图所示。

Set up a new worksheet (Sheet2) where the data entry will happen. Create data validation for the Address column as shown.

打开VBA编辑器并将该代码粘贴到模块中 Sheet2 。如果需要，您可以删除 Debug 语句。再次注意参考 E2 在 Sheet1 ：

Open the VBA editor and paste this code in the module for Sheet2. You can remove the Debug statement if you wish. Again note the reference to cell E2 on 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

Step 5

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

Step 5

Enjoy. Your data validation is now context sensitive. Examples:

这篇关于基于vlookup设置下拉列表值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！