我有 A 列，其中包含蘋果、香蕉、葡萄、李子、櫻桃等物品，
B1[column:row] 包含 A 列的下拉串列。我希望能夠根據 B1 中的值為 C1 然后 D1 等構建下拉串列 - 這意味著如果我在 B1 中選擇 Plums，則下拉串列在 C1 下，現在應該只有蘋果、香蕉、葡萄、櫻桃，那么如果我為 C1 選擇蘋果，D1 應該只有香蕉、葡萄、櫻桃
這可能嗎，如果可以的話，任何幫助將不勝感激
uj5u.com熱心網友回復：
EXCEL 縮小動態下拉選單
因此，您有 A 列，其中包含Apples、Bananas、Grapes、等專案Plums，Cherries如下面的螢屏截圖所示，
請按照步驟完成任務，
? 在單元格H1中輸入以下公式并向下填充，
=IF(COUNTIF($B$1:$B$5,A1)>=1,"",ROW())
? Enter the following formula in cell I1 & Fill Down,
=IF(ROW(A1)-ROW($A$1) 1>COUNT(H$1:H$5),"",INDEX(A:A,SMALL(H$1:H$5,1 ROW(A1)-ROW($A$1))))
? Now goto Formulas Tab --> Click Name Manager --> New --> Name --> Fruits_1 --> Enter the below formula in Refers To:
=OFFSET($I$1,0,0,COUNTA($I$1:$I$5)-COUNTBLANK($I$1:$I$5),1)
? Now goto cell C1, then from Data Tab --> Click Data Validation --> Settings --> Allow --> List --> Source --> Press F3(Function Key) --> Select Fruits_1 --> Press Ok
Note that cell B1 already contains a dropdown list of items from Column A
The drop down list have been created in the cell C1, and now one item is selected from the drop down, the used name in B1 is removed from the list and it only shows the names that have not been used, refer image below
In the same, way enter formula in cell J1 & K1 & Fill Down
Formula in Cell J1
=IF(OR(COUNTIF($C$1:$C$5,A1)>=1,COUNTIF($B$1:$B$5,A1)>=1),"",ROW())
Formula in cell K1
=IF(ROW(A1)-ROW($A$1) 1>COUNT(J$1:J$5),"",INDEX(A:A,SMALL(J$1:J$5,1 ROW(A1)-ROW($A$1))))
Like wise, again in Name Manager for the Fruits_2, enter the below formula,
=OFFSET($K$1,0,0,COUNTA($K$1:$K$5)-COUNTBLANK($K$1:$K$5),1)
Select cell D1 & create Data Validation likewise as done before for C1
Therefore the final task has been accomplished now, refer the image,
B1 --> Plums,
C1 --> Apples,
D1 --> ? Bananas ? Grapes ? Cherries
Important Note: Don't delete the columns H, I, J, & K the one created in above steps, if removed them, the drop down list will be invalid.
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/439909.html
標籤：擅长