numbers, text

[jamalhakem]

Hi
Any help?
I have this formula in E8, f8,…to x8 sheet2:
=IF(OR(ISNUMBER('sheet1'!E8);ISTEXT(' sheet1'!E8));' sheet1'!E8;"")
In AH8 I have this formulae, as array formulae:
=IF(E8<50;$E$7;"")&" "&IF(F8<50;$F$7;"")&" "&IF(G8<50;$G$7;"")&" "&IF
(H8<60;$H$7;"")&" "&IF(I8<60;$I$7;"")&" "&IF(J8<60;$J$7;"")&" "&IF
(K8<60;$K$7;"")&" "&IF(R8<50;$R$7;"")&" "&IF(S8<50;$S$7;"")&" "&IF
(T8<50;$T$7;"")&" "&IF(U8<60;$U$7;"")&" "&IF(V8<60;$V$7;"")&" "&IF
(W8<60;$W$7;"")&" "&IF(X8<60;$X$7;"")
Which checks the score and gives the name of the subject if it does
not meet the if check
It works perfectly when there is only numbers, but sometimes I have
instead of a score (number), a text such as (absent), and I want to
return the name of the subject like ($E$7). In E8 could be a number or
a text, or it can be empty “” the result of the first formula, so I
tried this

=IF(OR(E8<50;ISTEXT(E8));$E$7;"")&" &IF(OR(F8<50;ISTEXT(F8));$F$7;"")
&" "&IF(OR(G8<50;ISTEXT(G8));$G$7;"")&" "&IF(OR(H8<50;ISTEXT(H8));$H
$7;"")&" "&IF(OR(I8<50;ISTEXT(I8));$I$7;"")&" "&IF(OR(J8<50;ISTEXT
(J8));$J$7;"")&" "&IF(OR(K8<50;ISTEXT(K8));$K$7;"")&" "&IF(OR
(R8<50;ISTEXT(R8));$R$7;"")&" "&IF(OR(S8<50;ISTEXT(S8));$S$7;"")&" "&IF
(OR(T8<50;ISTEXT(T8));$T$7;"")&" "&IF(OR(U8<50;ISTEXT(U8));$U$7;"")&"
"&IF(OR(V8<50;ISTEXT(V8));$V$7;"")&" "&IF(OR(W8<50;ISTEXT(W8));$W$7;"")
&" "&IF(OR(X8<50;ISTEXT(X8));$X$7;"")
But it gives all the names of the subjects even if it is empty since
the formulae is giving “” which is text, what can I do to make it work
with numbers, text or “”.
Thanks in advance
Jam

 

[Shane Devenshire]

Hi,

How about

=IF(ISNUMBER(Sheet2!E8),Sheet2!E8,"")

And unrelated you could simplify your other formula to

=IF(E8<50,$E$7," ")&IF(F8<50,$F$7," ")&IF(G8<50,$G$7," ")&IF(H8<60,$H$7,"
")&IF(I8<60,$I$7," ")&IF(J8<60,$J$7," ")&IF(K8<60,$K$7," ")&IF(R8<50,$R$7,"
")&IF(S8<50,$S$7," ")&IF(T8<50,$T$7," ")&IF(U8<60,$U$7," ")&IF(V8<60,$V$7,"
")&IF(W8<60,$W$7," ")&IF(X8<60,$X$7,"")