EXCEL如何让”通过公式获取数据“的数据源作为下拉菜单的来源,并且是...
发布网友
共5个回答
热心网友
这个问题，颇有点难点，但很意义，考虑后还是共享一下，希望对你有用。
核心用 INDIRECT(ref_text, [a1])函数 来实现，一级菜单来自表第一行 1:1, 二级菜单来自一级菜单的对应明细内容，注考虑到你的数据是来自公式offset()取得的，参数前加上完全路径，如A1:D1---sheet1!A1:D1,
操作：1、整理数据源表，如图，F列不要有合并单元格；2、一级菜单定义：选择区域F2:F9---数据---数据有效性---允许：序列---数据来源：sheet1!A1:D1 (为了以后扩展，范围参数改大一点，如A1:AX1 )
2、二级菜单定义：一行一行定义，定位到G2，数据---数据有效性---允许：序列---数据来源：=INDIRECT($F$2)，G3---=INDIRECT($F$3)..... 不能下拉;
3、英文名称根据F列的值，用Vlookup( )函数匹配，H2=VLOOKUP(F2,Sheet2!F2:G20,2,0)
热心网友
是否允许用VBA解决？否则搞不定。
热心网友
有效性里，要用 偏移+counta函数，就没空行出现
热心网友
选中F2:F1000，直接进行 数据验证设置，序列里的公式(禁止G列有数据时更改F列数据)：
=IF(ISBLANK(G2),OFFSET($A$1,,,1,LOOKUP(1,0/($A$1:$E$1<>""),COLUMN($A:$E))))
选中G2:G1000，直接进行 数据验证设置，序列里的公式：
=OFFSET($A$1,1,MATCH(F2,$A$1:$C$1,)-1,(LOOKUP(1,0/($A$1:$A$1000<>""),ROW($1:$1000))-1)/2,1)
3.H2粘贴公式：
=OFFSET($A$1,(LOOKUP(1,0/($A$1:$A$1000<>""),ROW($1:$1000))-1)/2+MATCH(G2,OFFSET(A:A,,MATCH(F2,$A$1:$E$1,)-1),)-1,MATCH(F2,$A$1:$E$1,)-1)
下拉
4.如果F列要联想输入，需辅助列，J1输入公式：
=IFERROR(INDEX($A$1:$E$1,SMALL(IF(ISNUMBER(SEARCH(INDIRECT(CELL("address")),$A$1:$E$1)),COLUMN($A:$E)),ROW(A1))),"")
ctrl+shift+回车，下拉16384行(忽略循环引用提醒)
选中F2:F1000，直接进行 数据验证设置，序列里的公式：
=IF(ISBLANK(G2),OFFSET($J$1,,,LOOKUP(1,0/($J$1:$J$16384<>""),ROW($1:$16384))))
注：a.如果不想在F列单元格输入联想字符而直接显示全部列表，需双击如何空白单元格（包括要输入的单元格），或按delete、F2、F9或退格键
b.设置时G列需置空，且取消出错禁止选项
热心网友
建议直接用代码。
