List between two values

[Pofski]

Hello everybody,

i remember seeing this somewhere here before, but i seem to be unable to find it.

I am trying to create a dynamic list between two values
In the attached example you would be able to select a continent (I1), and then all the countries of that continent would be below.

Now i know you could just make a named range for each, but this is just an example. The actual list has a 40 some selections, and the list is constantly changing.

I was trying to use the combination of cell with offset, but i'm getting nowhere.
I need to have it start 1 below the selection in I1, and stop 1 above the next option on the list G1:G11

 

[NARAYANK991]

Hi Pofski ,

See if this is OK.

Narayan

 

[Pofski]

Narayan,

the information that is now in the second drop down should fill in the area I3:I66.
This is because this information would be compared with another list of filtered information, to check for differences.

Interesting way how you did this though. Never thought to do it like that.

 

[NARAYANK991]

Hi Pofski ,

Select the range I3:I66 , type in the formula :

=List_of_Countries

and array-enter it by using CTRL SHIFT ENTER.

You should see the list. If the number of countries is not enough , the remaining cells in the range will have #N/A error values.

Narayan

 

[Somendra Misra]

Hello @NARAYANK991 Sir,


Your formula is not working If I select the last choice from the first list. Can you please have a look again at it.

@Pofski

See the attached file. There are some modification in it. Column C&H added as helper. Than rows mentioning only, AMERICA and OCEANA has been removed. See if you can adapt this solution.

Regards,

 

[NARAYANK991]

Hi Misra ,

It is clear why this is happening ; change the definition of the named range List_of_Countries to :

=INDEX(Sheet1!$B:$B,MATCH(Selected_Item,Sheet1!$B:$B,0)+1):INDEX(Sheet1!$B:$B,IFERROR(MATCH(INDEX(First_Level_List,MATCH(Selected_Item,First_Level_List,0)+1),Sheet1!$B:$B,0),COUNTA(Sheet1!B:B)))

Narayan