Office 技巧-Exccel 表Config!E3:E256数据验证=item!C2:C100; 根据Config!E3-E256列的选择,决定Config!F3-F256和Config!G3
需求
表Config!E3:E256数据验证=item!C2:C100;
根据Config!E3-E256列的选择,决定Config!F3-F256和Config!G3-G256
要求下拉选择为对应item表的数据,使用的是office365版本,请注意excel的限制
Config!E3=item!C3时,Config!F3为item!D2:D100,Config!G3为item!G2:G100;
Config!E3=item!C4时,Config!F3为item!E2:E100,Config!G3为item!G2:G100;
Config!E3=item!C5时,Config!F3为item!F2:F100,Config!G3为item!G2:G100;
Config!E3=item!C6时,Config!F3为item!H2:H100,Config!G3为item!I2:I100;
Config!E3=item!C7时,Config!F3为item!J2:J100,Config!G3为item!K2:K100;
Config!E3=item!C8时,Config!F3为item!L2:L100, Config!G3为Y2:Y100;
Config!E4=item!C3时,Config!F3为item!D2:D100,Config!G3为item!G2:G100;
Config!E4=item!C3时,Config!F3为item!D2:D100,Config!G3为item!G2:G100;
Config!E4=item!C4时,Config!F3为item!E2:E100,Config!G3为item!G2:G100;
Config!E4=item!C5时,Config!F3为item!F2:F100,Config!G3为item!G2:G100;
Config!E4=item!C6时,Config!F3为item!H2:H100,Config!G3为item!I2:I100;
Config!E4=item!C7时,Config!F3为item!J2:J100,Config!G3为item!K2:K100;
Config!E4=item!C8时,Config!F3为item!L2:L100, Config!G3为Y2:Y100;
一直至256行...,
=E_ValidationRange
=CHOOSE(MATCH(Config!D3, item!$C$3:$C$20, 0),item!$D$2:$D$100,item!$E$2:$E$100,item!$F$2:$F$100,item!$H$2:$H$100,item!$J$2:$J$100,item!$L$2:$L$100,item!$O$2:$O$100,item!$P$2:$P$100,item!$Q$2:$Q$100,item!$R$2:$R$100,item!$S$2:$S$100,item!$T$2:$T$100,item!$U$2:$U$100,item!$V$2:$V$100,item!$W$2:$W$100,item!$X$2:$X$100,item!$Y$2:$Y$100,item!$Z$2:$Z$100)
=G_ValidationRange
=CHOOSE(MATCH(Config!E3, item!$C$3:$C$8, 0),item!$G$2:$G$100,item!$G$2:$G$100,item!$G$2:$G$100,item!$I$2:$I$100,item!$K$2:$K$100,item!$X$2:$X$100)