Hi

Meant to say, will take out numerics and lower case, will still not trap for
upper alpha's not in required range of values, but they could be accounted
for by Data Validation.

Regards

Roger Govier


Roger Govier wrote:
Hi Biff

If the data isn't already being validated then
{=CHAR(MIN(IF(CODE(Leave!E5:E9&"X")64,CODE(UPPER( Leave!E5:E9&"X"))))))}
should do the trick.

Regards

Roger Govier


Biff wrote:

One way would be the array formula
{=CHAR(MIN(IF(ISTEXT(Leave!E5:E9&"X"),CODE(Leave!E 5:E9&"X"))))}



That's case sensitive and also allows for entries other than the 6
specific variables (including numbers). I guess data validation could
take care of that.

a returns X
B returns B
3 returns 3

Biff

"Roger Govier" wrote in message
...

Hi

One way would be the array formula
{=CHAR(MIN(IF(ISTEXT(Leave!E5:E9&"X"),CODE(Leave!E 5:E9&"X"))))}

Commit with Ctrl+Shift+Enter not just Enter when entering or
modifying the formula. Excel will include the curly braces { } for
you, do not type them yourself.

Regards

Roger Govier


Kypreo wrote:

i have the folling formula, but excel doesn't seem to accept it

=IF(Leave!E5="A","A",IF(Leave!E5="I","I",IF(Leave! E5="S","S",IF(Leave!E5="L","L",IF(Leave!E5="T","T" ,IF(Leave!E5="R","R",IF(Leave!E6="A","A",IF(Leave! E6="I","I",IF(Leave!E6="S","S",IF(Leave!E6="L","L" ,IF(Leave!E6="T","T",IF(Leave!E6="R","R",IF(Leave! E7="A","x")))))))))))))


i need to have all those letters for each of the 5 cells being
referenced... currently using Excel 2000 9.0.6926 SP-3.

if there is a limitation.. how do i get around it ?