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 ?

Hi!

This is my best guess at what you want:

If any cell in the range Leave!E5:E9 contains any one of the letter
variables, return that letter, otherwise, return "X". Only one cell may
contain a letter.

List the letter variables in a range somewhere, say, A1:A6.

=IF(SUMPRODUCT(COUNTIF(A1:A6,Leave!E5:E9)),INDEX(L eave!E5:E9,MATCH("*",Leave!E5:E9,0)),"X")

There is a limit of 7 nested levels of functions. you had 12.

Biff

"Kypreo" wrote in message
...
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 ?

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 ?

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 ?

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 ?

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 ?