Using full names in dropdown list

Hello Everyone,

I use Excel 2003 on Windows xp

This is my question:

I have these 3 Regions (the group name is, Regions) as a dropdown list in I4
Americas
AsiaPacificRegion
EuropeMiddleEastAndAfrica

I have a dropdown list dependand on this one containing 196 Countries divided over these 3 Regions. currently =INDIRECT(I4) in the cell J4
What code could do this? (thus a space before every capital letter exept the first of course, and a - between MiddleEast) I really do not know how that works (i do know it is possible) Please explain widely.

Thank you in Advance

PabloV,

Welcome to the forum. Please read the forum rules below and then add the link to the other excel forum where you have posted this question

VBA Noob

G'day PabloV,

If I've understood you correctly, that you want spacing in the data validation (dropdown box).

You want the dropdown box to look like this (for example)

Americas
Asia & Pacific Region
Europe, Middle East & Africa

Your group name is, Regions and the range is Z1:Z3
Americas
AsiaPacificRegion
EuropeMiddleEastAndAfrica

Put the new look list you want the at Y1:Y3

Col Y                                           Col Z
Americas                                     Americas
Asia & Pacific Region                     AsiaPacificRegion
Europe, Middle East & Africa           EuropeMiddleEastAndAfrica

Now change the cell range of 'Regions' to the new look list (Y1:Y3)
Then in the data validation at cell J4 change =INDIRECT(I4) to

=INDIRECT(VLOOKUP(I4,X1:Z3,2,FALSE))

Remember to enter your cell reference to suit your data layout.

HTH