ARG! Pull my hair out. Null fields and formulas

[BrianC]

OK that works, but why do the same formulas yield different results in
two sheets? The reason I hesitate to use your solution is that it is a
very large spreadsheet that will need a bunch of editing. Is there
'hidden' configurable items that control this per sheet somewhere?

 

[Aladin Akyurek]

OK that works, but why do the same formulas yield different results in
two sheets? The reason I hesitate to use your solution is that it is a
very large spreadsheet that will need a bunch of editing. Is there
'hidden' configurable items that control this per sheet somewhere?

You noted:

A1 ---> =""

which makes A1 a non-numeric cell (text). That's why you get #VALUE!
when attempting =A1*5.