Names from column based on Country.

[rajeshn_in]

Hi Friends,

I want city names from Perticular country shown in column. Actually I know Indirect formula using back end data. Can I get directly from table.

Please see attachment for better understanding.

[FDibbins]

This ARRAY formula will give you a list of all cities...
=IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$9=$B$14,ROW($A$2:$A$9)),ROWS($A$1:A1))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[rajeshn_in]

Thank you FDibbins, I want Dropdown list for those cities.

[samba_ravi]

=INDEX($B$2:$B$9,MATCH($B$14,$A$2:$A$9,0)):INDEX($B:$B,LOOKUP(2,1/($A$2:$A$9=$B$14),ROW($A$2:$A$9)))
Try this formula in data Validation
Data Validation >Settings > Allow = List > Source = =INDEX($B$2:$B$9,MATCH($B$14,$A$2:$A$9,0)):INDEX($B:$B,LOOKUP(2,1/($A$2:$A$9=$B$14),ROW($A$2:$A$9)))

[shukla.ankur281190]

Hi rajesh,

As Mr. Ford suggested formula will give you the list of searched country's city lists. I have used his formula in E2 and drag it down.

Now you must create a name manager , do press- Alt-M-N->New->Name ( can use whatever you want without any space) and put =OFFSET($E$2,,,COUNTIF(E:E,"*?")) refers to section.

On b15 cell press -> Alt-A-V-V -Allow-List->Source- Press F3-> Select the same name what you created in name manager then ok.

That's it.

Check the attached file.

[rajeshn_in]

Hi nflsales, Below message coming:
You may not use reference operators (such as unions, intersections, and ranges) or array constants for data validation criteria.

[samba_ravi]

hi nflsales, below message coming:
You may not use reference operators (such as unions, intersections, and ranges) or array constants for data validation criteria.

see the attached file

[rajeshn_in]

Hi nflsasles, Attached file have some problem. Not opening.