如何解决Excel中dependent dropdown list 的问题

Question

现在我有一个工作簿，第一个表格是需要手动填写的（List)
剩余其他表格都需要根据这个表格的内容来填写。

List表格如下：
Plan Name Project Name Category
Social Media Mkt Instagram Placement Install Payments
Social Media Mkt Facebook Ads Subscription
Social Media Mkt Facebook Ads Lump sum
Email Mkt| Newsletter| Monthly Fee
Email Mkt| Promotional Emails Campaign Costs
Email Mkt Promotional Emails Rewards
Door Knock Word of mouth Deposit
Door Knock Special Envent Donation

接下来其他5个worksheet里分别有不同的数据填写，但是首先要有dropdown list，
第一个plan Name就是根据list里面的unique的name,在这个例子中有3个分别是[Social Media Mkt],[Email Mkt],[Door Knock].
第二列里需要根据第一列的值来确定下拉菜单，然后第三列根据第二列依次类推，请问怎么完成第二列和第三列的dropdown list且能应用在所有表格里？

Answer 1

在Excel中创建基于另一个工作表的依赖下拉列表，通常需要使用VBA（Visual Basic for Applications）脚本。下面是一个简单的VBA脚本示例，它将创建一个依赖下拉列表，并适用于所有工作表。
首先，您需要找到“开发工具”选项卡，如果它不在您的Excel界面上，您可以通过以下步骤启用它：

1. 转到“文件”菜单。
2. 选择“选项”。
3. 在“高级”选项卡中，勾选“启用开发工具栏”复选框。
4. 关闭选项对话框。
   现在，您应该可以在功能区中看到“开发工具”选项卡。
   接下来，您可以使用以下VBA脚本：

   Sub CreateDependentDropdowns()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim listRange As Range
    Dim dropdownRange As Range
    Dim dependentDropdown As ListObject
    
    ' 假设List工作表在A1:D11区域，且您想要在第一个工作表的A1单元格创建下拉列表
    Set listRange = ThisWorkbook.Worksheets("List").Range("A1:D11")
    
    ' 遍历所有工作表
    For Each ws In ThisWorkbook.Worksheets
        ' 获取工作表中的最后一个非空单元格
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        
        ' 创建第一个下拉列表
        Set dropdownRange = ws.Range("A1:A" & lastRow)
        Set dependentDropdown = ws.ListObjects.Add(xlSrcRange, dropdownRange, , xlYes)
        dependentDropdown.Name = "PlanName"
        
        ' 设置下拉列表的依赖关系
        ws.ListObjects("PlanName").ListColumns("Plan Name").Dependency = listRange
        
        ' 重复上述步骤创建其他列的下拉列表
        Set dropdownRange = ws.Range("B1:B" & lastRow)
        Set dependentDropdown = ws.ListObjects.Add(xlSrcRange, dropdownRange, , xlYes)
        dependentDropdown.Name = "ProjectName"
        ws.ListObjects("ProjectName").ListColumns("Project Name").Dependency = listRange
        
        Set dropdownRange = ws.Range("C1:C" & lastRow)
        Set dependentDropdown = ws.ListObjects.Add(xlSrcRange, dropdownRange, , xlYes)
        dependentDropdown.Name = "Category"
        ws.ListObjects("Category").ListColumns("Category").Dependency = listRange
    Next ws
   End Sub
   

   将这段代码复制到Excel的VBA编辑器中，然后运行它。这将在所有工作表的指定区域创建依赖下拉列表。
   请注意，您需要根据实际情况调整列表范围（listRange）和下拉列表范围（dropdownRange）。此外，如果您的工作表结构不同，您可能需要调整脚本以适应您的具体需求。
   如果您不熟悉VBA，或者想要以编程方式自动完成这个任务，您可以考虑使用Python和其Excel库，如openpyxl或pandas，但这样可能会更加复杂。