Mutiple drop down boxes

Dear All,

new to the forum and hoping to contribute as well as receive, i have an excel problem that i've trawled endlessly for a solution for after trying everything i could think of- including through many of this forum's threads.

I need to have 3 cells with drop downs offering say apples, oranges, pears, mangos. If the 1st drop down has apples selected, I need the second to only offer oranges, pears and mangos. If the second has oranges selected, I need the 3rd to only offer pears and mangos.

I have tried all the types of data validation I know.

I have tried doing it using advanced filters and removing duplicates/ only including unique values by adding the selection made in the 1st input to the second drop down's list but all that does is give me the whole list again.

Any help would be greatly appreciated.

i should add my skills do not include VBA.

Tharts

Hi

Try this

Sheet2:
A1:A4 has your values apples, oranges, pears, mangoes
B1: =COUNTIF(Sheet1!A:A,Sheet2!A1) Copy down to B4
C1: =IF(B1,"",ROW()) Copy down to C4
E1: =INDEX(A:A,SMALL(C:C,ROW())) Copy down to E4
G1: =COUNTIF(B:B,0) This cell also has the defined name fruitcnt
E1: Also has a defined name of Fruit

Sheet1:
A1: has Data Validation, list, with the formula =OFFSET(Fruit,0,0,fruitcnt,1)
Copy from A1 down to A4

Now if you make a selection from A1, it should give you all 4 options. Make a selection, then move to A2. Now you should only have 3 options...

HTH

rylo

Dear Rylo,

Many thanks. Your directions allowed me to perfectly create what i was after - which in itself has progressed me far - thank you.

Due to skill level however, when i try to emulate it into a much wider spreadsheet and replace some of the general cells (eg:A:A, B:B etc) with specific array ranges I am not able to replicate. Basically all works except for the replaced column E (which in turn then creates issues for the drop down box options). My best guess is that it is due to not having specific cell references after one/more of the ROW() inputs. Would you have any thoughts?

Again thanks for the help.

tharts

Dear Rylo and FDibbins,

My thanks to both of you. FD, your post helped me really consider the problem and as stated Rylo, your's gave me a solution that just needed some modifying to allow it to be inserted into a much larger spreadsheet. The modification i came up with which seems to work for others who may need similar is:

Sheet2:
A1:A4 has your values apples, oranges, pears, mangoes
B1: =COUNTIF(Sheet1!A:A,Sheet2!A1) Copy down to B4 Noting that Sheet2!A1 would be the reference to drop down box input
C1: =IF(B1,"",ROW()) Copy down to C4
D1: =INDEX(A:A,SMALL($C$1:$C$4,ROWS(D$1:D1))) Copy down to D4
E1: =COUNTIF($B$1:$B$4,0) This cell also has the defined name fruitcnt
D1: Also has a defined name of Fruit

Sheet1:
A1: has Data Validation, list, with the formula =OFFSET(Fruit,0,0,fruitcnt,1) Nb. A1 would be the drop down input cell wherever that may be located.
Copy from A1 down to A4