J
JeffJ
Previously I had this formula which was working fine:
=IF(AND(OR($E$5=1,$E$5=2),$E$2="...3/12...",$E$3="
inch"),VLOOKUP($E$6,lookup!$C$3:$D$19,2,0),IF(AND($E$5=3,$E$2="...3/12...",$E$3="
inch"),VLOOKUP($E$6,lookup!$E$3:$F$19,2,0),""))
But after I add another part onto the end of the formula I am no
getting a #VALUE! error (even though, if I enter information in th
referenced cells, it seems to work fine and return the correc
numbers):
=IF(AND(OR($E$5=1,$E$5=2),$E$2="...3/12...",$E$3="
inch"),VLOOKUP($E$6,lookup!$C$3:$D$19,2,0),IF(AND($E$5=3,$E$2="...3/12...",$E$3="
inch"),VLOOKUP($E$6,lookup!$E$3:$F$19,2,0),""))
IF($E$16=$C$33,$E$17,0) + IF($E$18=$C$33,$E$19,0)
IF($E$20=$C$33,$E$21,0) + IF($E$22=$C$33,$E$23,0)
IF($E$24=$C$33,$E$25,0) + IF($E$26=$C$33,$E$27,0)
IF($E$28=$C$33,$E$29,0)
I have several columns of such formulas, and I can use Conditiona
Formatting to hide the #VALUE! error, which is fine.
However, at the far right I have another column that uses this formul
and also returns the #VALUE! error (I assume it's merely reflecting th
#VALUE! error in the other cells):
=SUM(E34:K34)
The problem is, in the SUM formula, Conditional Formatting does no
seem to be able to hide the #VALUE! error (oddly enough).
With both formulas, I have experimented with Ctrl-Shift-Enter to see i
that would get rid of the error (even though I don't intentionally wan
any of the formulas to be arrays), but I still get the #VALUE! error.
Can someone please tell me either:
-Why I am getting the #VALUE! error in the first place, and how t
prevent it;
-OR-
-How to make the #VALUE! error invisible in the SUM formula (like
said, Conditional Formatting does not seem to work, unless I'm doing i
wrong; but Conditional Formula does hide the error with the IF formul
cells).
Thank you
=IF(AND(OR($E$5=1,$E$5=2),$E$2="...3/12...",$E$3="
inch"),VLOOKUP($E$6,lookup!$C$3:$D$19,2,0),IF(AND($E$5=3,$E$2="...3/12...",$E$3="
inch"),VLOOKUP($E$6,lookup!$E$3:$F$19,2,0),""))
But after I add another part onto the end of the formula I am no
getting a #VALUE! error (even though, if I enter information in th
referenced cells, it seems to work fine and return the correc
numbers):
=IF(AND(OR($E$5=1,$E$5=2),$E$2="...3/12...",$E$3="
inch"),VLOOKUP($E$6,lookup!$C$3:$D$19,2,0),IF(AND($E$5=3,$E$2="...3/12...",$E$3="
inch"),VLOOKUP($E$6,lookup!$E$3:$F$19,2,0),""))
IF($E$16=$C$33,$E$17,0) + IF($E$18=$C$33,$E$19,0)
IF($E$20=$C$33,$E$21,0) + IF($E$22=$C$33,$E$23,0)
IF($E$24=$C$33,$E$25,0) + IF($E$26=$C$33,$E$27,0)
IF($E$28=$C$33,$E$29,0)
I have several columns of such formulas, and I can use Conditiona
Formatting to hide the #VALUE! error, which is fine.
However, at the far right I have another column that uses this formul
and also returns the #VALUE! error (I assume it's merely reflecting th
#VALUE! error in the other cells):
=SUM(E34:K34)
The problem is, in the SUM formula, Conditional Formatting does no
seem to be able to hide the #VALUE! error (oddly enough).
With both formulas, I have experimented with Ctrl-Shift-Enter to see i
that would get rid of the error (even though I don't intentionally wan
any of the formulas to be arrays), but I still get the #VALUE! error.
Can someone please tell me either:
-Why I am getting the #VALUE! error in the first place, and how t
prevent it;
-OR-
-How to make the #VALUE! error invisible in the SUM formula (like
said, Conditional Formatting does not seem to work, unless I'm doing i
wrong; but Conditional Formula does hide the error with the IF formul
cells).
Thank you