dropdown list problem

[aamir]

I have A column Countries & B Column Cities upto 50

I want D1 when i select any country the E1 list should only show based on my D1 selection.

2nd thing i my D1 list there are empty spaces, how to remove these empty spaces?

 

[Marcelo Branco]

Hi,

Take a look at
http://www.contextures.com/xldataval02.html

HTH

M.

 

[aamir]

thanks branco but still i can't do it on my sheet.

=INDIRECT(A2)

OR

  [LEFT][COLOR=black][FONT=Arial]=OFFSET(INDIRECT(SUBSTITUTE($F2," ","")),0,0,COUNTA(INDIRECT(SUBSTITUTE($F2," ","")&"Col")),1)[/FONT][/COLOR][/LEFT]

doesn't bring any values.