I have column A with items such as Apples, Bananas, Grapes, Plums, Cherries,

B1[column:row] contains a drop drop of column A. I want to be able to build a drop down for C1 then D1, etc... based on the values in B1 - meaning if I select Plums in B1, the drop down in C1 should now have just Apples, Bananas, Grapes, Cherries, then if I choose Apples for C1, D1 should have only Bananas, Grapes, Cherries

Is this possible, if so any help wold be greatly appreciated

CodePudding user response：

EXCEL DWINDLING DYNAMIC DROP DOWNS

So you have column A with items such as Apples, Bananas, Grapes, Plums, Cherries as shown in the screenshot below,

Please follow the steps to accomplish the task,

• Enter the below formula in cell H1 & Fill Down,

=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.