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屋!