Drop down menu for 2003 version

[PedroLA]

Hi All, again

I have another question for Excel 2003. I have seen advice on the 2007/ vista related to this. But I can not get it right on the 2003 version.

I have an extensive list of Countries, and when creating a validation data list, I have to scroll down through all of them to get for example to Zimbawe.

Any idea how can I get to all the countries starting with "Z" without scrolling all the way down???

Thanks
Pedro

 

[Trouttrap2]

Hello,
You can change the way your validation list is sorted. Sort you validation list source from Z to A for example. Or just have all you "z" at the top of your list and the rest sorted alphabetically. That's the best way I know of.

 

[PedroLA]

Thanks for the tip. I think I didn't help myself with the example.

This is to used on a daily basis, and is for all countries. I have a list of 124 countries. I have the drop down menu set up, but it doesn't recognise the first character of the country I'm looking for.

Any ideas?

Thanks

 

[shg]

If you're comfortable with a VBA solution, see

http://www.box.net/shared/4qxybepxxr for a ReadMe,
http://www.box.net/shared/e8dbbple34 for a workbook example, and
http://www.box.net/shared/ono8ni9xvr for the add-in that mechanizes the example.

 

[grizz]

say F1 is your title country -- name country then F2:F110 is your country's then highlight them and name them countrynames --- then your drop down in A2 -- Data / Validation / list and in the source type in

=offset(country,match(left(A2,Len(A2)),Left(countrynames,Len(A2)),0),0,sumproduct(--(left(countrynames,Len(A2))=A2)),1)

now when you type in the first letter it should only give you countrys that start with that letter

 

[Smitty]

Welcome to the Board!

If you use an ActiveX ComboBox you'll get that behavior. If I recall in 2003 it's under View-->Toolbars-->Control ToolBox. Then in the control properties just set the ListFillRange = to your Validation range.

Unfortunately, it's an object so it needs to be clicked (but then again, so does your validation list). Once you do that if you hit "z" you'll go right to the Z's.

HTH,

 

[PedroLA]

Hi All,

Thanks again for your help on this one.

Unfortunately, still not working.

I tried your 3 recomendations and no success yet.

@SHG, I can not get my head around the VBA solution for this one.

@grizz, my country list is on a seperate worksheet (Country List); on worksheet1 I have the drop down menu (validation list) on E2. The Validation List (countries) are called "Country_List". I have tried your Offset solution, but I guess I'm doing something wrong, as when I put the formula on the Source of the pop-up Validation window; the drop down manu on the spreadsheet shows your formula. Is this only if the list is on the same worksheet?

@Smitty, I thought yours was going to be the Billion dolar unswer, but unfortunately once I turn on the "Control Toolbox" Menu, on the properties option, it doesn't show any ListFillRange option for me to edit; nor does allow me to input this as an option. Any idea why?

It would be a great help if you could point me to a solution here.

Thank you already for your time.

Pedro

 

[GlennUK]

...once I turn on the "Control Toolbox" Menu ...

What are you doing exactly? You were told to use a ComboBox from the Control Toolbox toolbar. You then alter it's properties in Design mode.

 

[PedroLA]

Appologies on my poor description:

I used the ComboBox from the Control Toolbox toolbar. I tried to alter it's properties in Design mode. But it doesn't have the ListFillRange option for me to edit; nor does allow me to input it as an option.

Any ideas?

Thanks again

 

[GlennUK]

What name is showing in the first property of the Properties window?