Help with dropdown without using macro

Create a drop down list in Col K1 to k...., In Col J1 put 1 & Put the following formula in Col J2 and drag it down

Formula:

=IF(K2<>"",MAX(J1:J1)+1,"")

Now select b2 and goto data validation under data, Choose "List" from setting validation criteria, and in Source put the following formula

Formula:

=INDIRECT(ADDRESS(MATCH(A1,$K:$K,0)+1,11)&":"&ADDRESS(MATCH(MAX(J:J),$J:$J,0),11))

It will create a drop down list depending in the text in col a1

attached herewith your file for better understanding

Sorry i think i misread your question.
Create a drop down list in Col K1 to k.... (say one, two, three.....)
Now in B2

Formula:

=IFERROR(INDIRECT(ADDRESS(MATCH(A1,$K:$K,0)+1,11)),"")

and in b3

Formula:

=IFERROR(INDIRECT(ADDRESS(MATCH(B1,$K:$K,0)+1,11)),"")

and drag it down as per required.

Now if a1= "one", then b2>b6 shows "two, three, four...."
If a1="Two", then b2>b6 shows "Three, Four, five..."

In a2

Formula:

=IFERROR(INDIRECT(ADDRESS(MATCH(A1,$A$14:$A$30,0)+14,1)),"")

and drag it down

try this

Formula:

=IFERROR(IF(INDEX(A18:A36,MATCH($A$1,A18:A36,0)+ROW()-1,1)=0,"",INDEX(A18:A36,MATCH($A$1,A18:A36,0)+ROW()-1,1)),"")

in A2 and copied to a6

A little correction will do your work
You need to freeze the range

Formula:

=IFERROR(IF(INDEX($A$14:$A$32,MATCH($A$1,$A$14:$A$32,0)+ROW()-1,1)=0,"",INDEX($A$14:$A$32,MATCH($A$1,$A$14:$A$32,0)+ROW()-1,1)),"")

Its working for fine me. see the attachment

Glade it helped you.
Plz click on the * to add to my reputation or rank this thread.

If you get a solution to your problem, as per rule, plz mark this thread as solved
Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.