Excel动态下拉使用逗号分隔数据 [英] Excel Dynamic Drop Down Using Comma Delimited Data

问题描述

我正在尝试开发一种内部使用形式，其中个人可以从下拉列表中进行选择，这将根据他们的选择过滤另一个下拉列表。我已经审查了间接依赖动态列表的选项，但是对于可能包含数以千计（或更多）排列的数百个不同值的列表似乎不可行。我之前的小组创建了一堆宏来解决这个问题，但是我必须相信管理信息有一个更简单的方法。

I'm trying to develop an internal use form where individuals can make a selection from a drop down list that will filter another drop down list based on their choice. I have reviewed the options for indirect, dependent dynamic lists, but this does not seem feasible for a list that will potentially contain hundreds of different values with thousands (or more) permutations. The group before me created a bunch of macros to solve this issue, but I have to believe there is a simpler way to manage the information.

有没有办法可以从单个单元格中开发下拉列表？任何见解将不胜感激。

Is there a way that you can develop a drop down list from within a single cell? Any insight would be greatly appreciated.

更新：使用Gary代码的一部分，我能够解决部分问题：

UPDATE: Using part of Gary's code, I was able to solve part of my issue:

Sub Marco4()
    r = Range("H24").Value
    s = Application.VLookup(r, Worksheets("Data").Range("R2:T4"), 3, False)
    Range("B25").Select
    With ActiveCell.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=s
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

我想使用一个现有的下拉项目而不是单元格

I'd like to use an existing drop down item instead of having the cell turn into a drop down itself, if possible.

推荐答案

说单元格 C2 包含一个逗号分隔的字符串。这个简单的宏将将activecell的数据验证设置为该字符串：

Say cell C2 contains a comma separated string. This simple macro will set the Data Validation of the activecell to that string:

Sub LeeCarver()
    Dim s As String
    s = Range("C2").Value
    With ActiveCell.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=s
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

例如：

这篇关于Excel动态下拉使用逗号分隔数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！