Question

页: 1 我想根据另一个工作表格(<代码>Validation)对这两条加以验证,并注明相同的栏目。 www.un.org/Depts/DGACM/index_french.htm

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

...

大约有11,000个不同的区块,大约400个区块/楼梯。

我的目标是,如果用户在<代码>Block栏上输入的数值,则在<代码>Address栏上作出与<代码>相对应的下限选择。栏目。

我尝试采用这一公式进行习俗验证:

=VLOOKUP(UserEntry!A2,Validation! A2:B400

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.

如何使<代码>Address的审定下调包含与<代码>Block值相应的所有价值?

Answer 1

你没有提及VBA,但这里是使用它的解决办法。

Step 1

创立一个企业-压力关系总表。确保在<代码>Block上分类。 I used Sheet:

https://i.stack.imgur.com/3UVGu.png” alt=“Master table”/>

单元编号:E2很重要。你们实际上不需要做任何事情,但宏观将使用。页: 1 只是要表明这一点,但你将暂时使用这个公式(在这里作评论,以便你能够看到)。

Step 2

创造出一个标明范围。Refers to:是你在以上E3中所看到的,你可以在此看到“手机编码”的提及。方便之公式是

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

“Dynamic

Step 3

建立一个新的工作表格(Sheet2),数据输入将发生。为<代码>Address栏目建立数据验证。

“entergraph

Step 4

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

欢乐。你的数据验证现在具有敏感性。实例:

enter image description here

Answer 2

你们可以为此使用一个充满活力的点子。

Assumptions:

Validation list is sorted A..Z
Validation list is in range Validation!A:B
Validation list includes headings in row 1
On sheet UserEntry the Address cell to be validated is one cell to the right of the ented Block
Tried and tested on Excel 2010

创建Named range,作为验证来源(I ve used name ValList):

=OFFSET(Validation!$B$1,MATCH(INDIRECT(ADDRESS(CELL("row"),CELL("col")-1)),Validation!$A:$A,0)-1,0,COUNTIF(Validation!$A:$A,INDIRECT(ADDRESS(CELL("row"),CELL("col")-1))),1)

添加Data Validation到规定的牢房:List ,来源:=ValList

Uses INDIRECT, ADDRESS and CELL to get a refernce to the user entered Block relative to the active cell
Uses MATCH and COUNTIF to get the position and size of the matching Blocks in the validation list
Uses OFFSET to set the return range to the addreesses matching the enterd block

Step 1

Step 2

Step 3

Step 4

Step 5

友情链接