Excel - 值列表中的条目基于某些其他列值

We have a scenario in excel (2010) where the list of values present in a dropdown change dynamically based on some column of that row. For eg. Consider the "Supervisor" dropdown in sheet1 below:

我们在excel(2010)中有一个场景,其中下拉列表中存在的值列表会根据该行的某些列动态更改。例如。考虑下面的sheet1中的“Supervisor”下拉列表:

Emp Grade   Supervisor
A   14  
B   12  
C   13  
D   12  
E   12  
F   13  
G   14

Now let's say there is a dropdown for the supervisor. For every employee, the supervisor can be a person of his grade or higher grades only. So, for eg. For grade 13 employee, can have a supervisor with grade 13 or grade 14 only, not grade 12.

现在让我们说主管有一个下拉列表。对于每位员工,主管可以只是其成绩或更高年级的人。所以,对于例如。对于13年级的员工,可以只有13级或14级的主管,而不是12级。

How can I write a custom condition like this inside the list of values? I have tried with things like named range, offset etc. but none allows specifying custom conditions. Any help?

如何在值列表中编写这样的自定义条件?我尝试过命名范围,偏移等等,但没有人允许指定自定义条件。有帮助吗?

3 个解决方案

#1

I found the following document to be helpful in creating dependent Data Validation dropdowns: DV0064 - Dependent Lists Clear Cells, which can be downloaded here (for free):

我发现以下文档有助于创建依赖数据验证下拉列表:DV0064 - 依赖列表清除单元格,可在此处下载(免费):

http://www.contextures.com/excelfiles.html#DataVal

You can tailor the example to your needs.

您可以根据自己的需要定制示例。

#2

=OFFSET('validation pivot'!$A$1,0,1,COUNTIFS('validation pivot'!$A:$A,">="&B2),1)

The supervisor needs to be at least his pay grade (>=B2). In order to have it work you need to have the pivot inserted in validation pivot A1. How to create the pivot (hasty notes):

主管需要至少达到他的薪资等级(> = B2)。为了使其工作,您需要将枢轴插入验证枢轴A1中。如何创建枢轴(草率笔记):

add grade and emp 'emp as subset

将grade和emp'emp作为子集添加

tabular view 'to have separate columns

表格视图'有单独的列

repeat labels ' to be able to count them

重复标签'以便能够统计它们

remove autosums(both within and total) 'to not deal with evading it

删除autosums(包括内部和总计)'以避免逃避它

hide column labels and filters 'same

隐藏列标签和过滤器相同

descending order(grade) 'to get a simple match method

降序(等级)'得到一个简单的匹配方法

data: store none 'to refresh the descending order every time

data:store none'每次刷新降序

See uploaded sample file.

查看上传的示例文件。

#3

This code (column A = EMP, B = Grade, C = Supervisor)

此代码(A列= EMP,B =等级,C =主管)

Sub test()

Dim actualgrade As Integer
Dim lastRowA As Integer
Dim numbers As String

lastRowA = Sheets("sheet1").Cells(Sheets("sheet1").Rows.Count, "A").End(xlUp).Row

For i = 2 To lastRowA '1 = headers
actualgrade = Cells(i, 2)

For j = 2 To lastRowA
If Cells(j, 2) >= actualgrade Then
numbers = numbers & " " & Cells(j, 1).Value
End If
Next j

Cells(i, 3).Value = numbers
numbers = ""

Next i
End Sub

Makes this result:

得到这个结果:

Emp Grade   Supr
A   14   A G
B   12   A B C D R F G
C   13   A C F G
D   12   A B C D R F G
R   12   A B C D R F G
F   13   A C F G
G   14   A G

Feel free to change it like you need it

随意改变它就像你需要它

#1