Harlan:
Fortunately, The Transition Formula Evaluation box is NOT checked.
I used your suggestion, in place of my *******ized IF statement.
This one looks good and is easy to "read". I expect to use it lots more.
Thanks so much.

Again, I'd still like to get to the bottom of the problem.


"Harlan Grove" wrote:

BEEJAY wrote...
=IF(D337="Front",1,IF(D337="Rear",1,IF(D337="Both ",2,0)))
Formula created on new, blank sheet - Works Great
Copy to Contract Sheets - Big Problems
Formula will not return a Zero, (as expected) when D337 is Blank
....

Whenever a formula works differently on different worksheets, the
*FIRST* thing to check is the Transition Formula Evaluation setting.
Run the menu command Tools Options, select the Transition tab, and
check whether the box to the left of 'Transition formula evaluation'
near the bottom of the dialog is checked. If it is, that's the
problem. Transition Formula Evaluation means Lotus 123 Release 2.01 &
subsequent formula evaluation semantics, and that means *ALL* strings
equal numeric zero when compared to any number, and blank cells are
*ALWAYS* evaluated as numeric zero. Thus any string equals any blank
cell.

It may or may not be safe to disable Transition Formula Evaluation. You
could have other formulas that rely on it. That's up to you to check.

Another safe way to perform the same operation that should work
whatever this setting would be

=SUMPRODUCT(COUNTIF(D337,{"Front";"Rear";"Both"}), {1;1;2})