Dropdown list - formule to get only upper and lower case letters?

[dunnobe]

Dear all,

I would like to make 2 dropdown lists where the last one depends on the previous dropdown list and the first dropdown list depends on an initial value.

I kept the example a bit simple, but I'm facing numerous annoying characters like Char(10), spaces, numbers, underscore, ampersand, hyphen ... which are quite annoying in named ranges.

Is there a formule that allows me to only capture the upper and lower letters and disregards all of the other characters?
Perhaps ASCI signs from 60 to 90 and 97 to 122?

My incomplete effort:

Please Login or Register  to view this content.

Desired result:

Category Area	Category Group	Category Name
1. Rolling Stock (RST)	1.1. Acquisition of Rolling Stock	1.1.1. Procurement of Rolling Stock
	=INDIRECT(RollingStock)	=INDIRECT(AcquisitionofRollingStock)
3. Information and Communication Technology (ICT)	3.4. Datacenter	3.4.2. Storage & Back-up
	=INDIRECT(InformationandCommunicationTechnologyICT)	=INDIRECT(Datacenter)

Thanks and best regards,

Bram

[XLent]

Bram,

Perhaps, rather than the INDIRECT route, you could use something like the below?

With B2 the active cell apply following as DV source for your list:

=OFFSET(TAXONOMY!$A$5;1;MATCH($A2;mainCat;0)-1;COUNTIF(INDEX(TAXONOMY!$A$6:$F$16;0;MATCH($A2;mainCat;0));"?*");1)

you should find you can use a similar approach for your subsequent DV list using your drop down 2 table, e.g. with C2 now the active cell apply below as source for DV list:

=OFFSET(TAXONOMY!$H$5;1;MATCH($B2;Taxonomy!$H$5:$AN$5;0)-1;COUNTIF(INDEX(TAXONOMY!$H$6:$AN$38;0;MATCH($B2;Taxonomy!$H$5:$AN$5;0));"?*");1)

[dunnobe]

Thanks XLent,

It works just fine!

Reputation +1