Excel自动更新中的从属下拉列表

问题描述

我可以在excel中创建简单的依赖下拉列表，如下面的屏幕快照所示.

I can create simple dependent drop down list in excel as shown in the below screenshot.

http://s14.postimg.org/sip3g4nc1/dropdown1.jpg 如果我在单元格E1中选择TeamA，则TeamA的所有三个成员都显示在单元格F1的下拉列表中

http://s14.postimg.org/sip3g4nc1/dropdown1.jpgIf I select TeamA in the cell E1, all the three members of TeamA is shown in the drop down list at cell F1

现在我要实现以下方案-

Now I want to achieve the following scenarios -

1. 第一个成员将在F1中显示，并立即选择E1中的团队.目前，无论我在单元格E1处选择什么，F1都将保持不变，直到我通过单击F1的下拉列表更改值为止.

1. The first member will be shown in F1 immediately selecting Team in E1. Currently whatever I select at cell E1, F1 stays unchanged until I change the value by clicking F1's drop down list.

在G1和H1中还将有两个单元格来容纳成员.因此，将有三个单元供三个成员使用.当我在E1中选择团队时，将立即用相应的成员更新三个单元格(F1，G1和H1).每个成员单元都有一个下拉列表，其中包含所有成员以供以后选择所需的成员.
http://s16.postimg.org/zb2pja4hh/dropdown2.jpg

There will be another two cells to hold members in G1 and H1. So, there will be three cells for three members. When I select Team in E1, immediately three cells (F1, G1 & H1) will be updated with the corresponding members. Each member cell will have drop down list having all the members to select desired member later.
http://s16.postimg.org/zb2pja4hh/dropdown2.jpg

推荐答案

假定您使用了间接引用( TeamA 下的行被命名为 TeamA 等):

Assuming you used indirect referencing (the rows under TeamA are named TeamA and so on):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng(1) As Range, rng1 As Range
Set rng(0) = Range("E1") 'your primary selection
Set rng(1) = Range("F1:H1") 'your secondary selection range
Application.EnableEvents = False
If Not Intersect(Target, rng(0)) Is Nothing Then 'if you have changed your primary selection
    For Each rng1 In rng(1) 'each cell in your secondary selection
        i = i + 1
        rng1 = Range("" & rng(0).Value2)(i, 1) 'gets changed to the nth value in the indirect reference of the primary selection ("TeamA" 's second row is "MemberA2" for example)
    Next
End If
Application.EnableEvents = True
End Sub

您需要将此子项放入工作表模块中，并以 .xlsm 扩展名保存文件.

You need to place this sub into your worksheet module and save the file with .xlsm extension.

这篇关于Excel自动更新中的从属下拉列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！