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 !E5: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 !E5: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(Lea ve!E5="S","S",IF(Leave!E5="L","L",IF(Leave!E5="T", "T",IF(Leave!E5="R","R",IF(Leave!E6="A","A",IF(Lea ve!E6="I","I",IF(Leave!E6="S","S",IF(Leave!E6="L", "L",IF(Leave!E6="T","T",IF(Leave!E6="R","R",IF(Lea ve!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 ?