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列需置空,且取消出错禁止选项

热心网友

建议直接用代码。

声明声明:本网页内容为用户发布,旨在传播知识,不代表本网认同其观点,若有侵权等问题请及时与本网联系,我们将在第一时间删除处理。E-MAIL:11247931@qq.com