Hello.

I have a subroutine that uses a formula to determine if a cell is the same
as the one below it, and the formula, =EXACT(a1,a2) is evaluating properly in
some cases, and in others, it's returning #VALUE on the last row of data...
where it compares a cell with data, vs. a blank cell.

What I've noticed with the spreadsheets where it works is that the logical
functinos, =isnumber and =istext , etc., evaluate to TRUE or FALSE, as they
are supposed to. However, on the spreadhseets where =EXACT fails, the
logical functions evaluate to 1 or 0.

I know those are the same things, thinking in a Boolean manner, but it's
consistent. It appears to be newly created sheets that work fine, and sheets
that have had other data on them in the past which fail.

Is there a setting or property that you know of which would cause this
behavior?

Thanks,
Mark

For that troublesome workbook, try toggling:

Tools|Options|Transition tab|Uncheck "transition formula evaluation"



mark wrote:

Hello.

I have a subroutine that uses a formula to determine if a cell is the same
as the one below it, and the formula, =EXACT(a1,a2) is evaluating properly in
some cases, and in others, it's returning #VALUE on the last row of data...
where it compares a cell with data, vs. a blank cell.

What I've noticed with the spreadsheets where it works is that the logical
functinos, =isnumber and =istext , etc., evaluate to TRUE or FALSE, as they
are supposed to. However, on the spreadhseets where =EXACT fails, the
logical functions evaluate to 1 or 0.

I know those are the same things, thinking in a Boolean manner, but it's
consistent. It appears to be newly created sheets that work fine, and sheets
that have had other data on them in the past which fail.

Is there a setting or property that you know of which would cause this
behavior?

Thanks,
Mark

--

Dave Peterson

For that troublesome workbook, try toggling:
Tools|Options|Transition tab|Uncheck "transition formula evaluation"

Thanks Dave!

That's it, exactly. And, it makes sense... the file where it popped up was
one that was originally started from an old Lotus file.

Thank you!

I'd turn off all those transition settings -- especially if I'm not a regular
user of Lotus 123.

mark wrote:

For that troublesome workbook, try toggling:
Tools|Options|Transition tab|Uncheck "transition formula evaluation"

Thanks Dave!

That's it, exactly. And, it makes sense... the file where it popped up was
one that was originally started from an old Lotus file.

Thank you!

--

Dave Peterson

I'd turn off all those transition settings -- especially if I'm not a regular
user of Lotus 123.


Earlier today, I put a line in the subroutine to turn off that setting, so
that it can't cause a problem there any more.

We used to use Lotus here, but not now.

Thanks again.