Formula Result Correct but value in the cell is wrong
 

I am using the below formula to compare 2 worksheets for like data and am
getting 2 different errors and I am wondering if anyone has any suggestions.

The formula:
=OR(EXACT(A4, 'Completed Installs'!A2:A1690))

Error1
If you click on the formula and bring it up it will say that the formula
result is TRUE and this is correct as it found at least one line in column A
of the YTD Install worksheet that matched A4 in "Installs For 2004". However
the printed cell value is "FALSE" and if the formula is TRUE why is it
printing FALSE?

Error 2
I have 2067 lines in "Installs For 2004" that I need compared to "Completed
Installs" which only has 1690 lines. Once the formula hits 1691 in "Installs
For 2004" I get and error of #VALUE.

I'm telling it the range I need it to look but it looks like it is trying to
match the same line number up in both errors and that is not what I'm trying
to do. I'm just trying to compare what installs I have for the year that
needs to be done to what installs are completed where column A contains the
same value for each install such as a Install Number.

Any help would be greatly appreciated.

Thank you,

Jim

Use COUNTIF instead,

=COUNTIF('Completed Installs'!A2:A1690,A4)0

OTOH your formula probably works if you enter it with
ctrl + shift & enter


Regards,

Peo Sjoblom

"jac" wrote:

I am using the below formula to compare 2 worksheets for like data and am
getting 2 different errors and I am wondering if anyone has any suggestions.

The formula:
=OR(EXACT(A4, 'Completed Installs'!A2:A1690))

Error1
If you click on the formula and bring it up it will say that the formula
result is TRUE and this is correct as it found at least one line in column A
of the YTD Install worksheet that matched A4 in "Installs For 2004". However
the printed cell value is "FALSE" and if the formula is TRUE why is it
printing FALSE?

Error 2
I have 2067 lines in "Installs For 2004" that I need compared to "Completed
Installs" which only has 1690 lines. Once the formula hits 1691 in "Installs
For 2004" I get and error of #VALUE.

I'm telling it the range I need it to look but it looks like it is trying to
match the same line number up in both errors and that is not what I'm trying
to do. I'm just trying to compare what installs I have for the year that
needs to be done to what installs are completed where column A contains the
same value for each install such as a Install Number.

Any help would be greatly appreciated.

Thank you,

Jim

Thank you, That works great!

"Peo Sjoblom" wrote:

Use COUNTIF instead,

=COUNTIF('Completed Installs'!A2:A1690,A4)0

OTOH your formula probably works if you enter it with
ctrl + shift & enter


Regards,

Peo Sjoblom

"jac" wrote:

I am using the below formula to compare 2 worksheets for like data and am
getting 2 different errors and I am wondering if anyone has any suggestions.

The formula:
=OR(EXACT(A4, 'Completed Installs'!A2:A1690))

Error1
If you click on the formula and bring it up it will say that the formula
result is TRUE and this is correct as it found at least one line in column A
of the YTD Install worksheet that matched A4 in "Installs For 2004". However
the printed cell value is "FALSE" and if the formula is TRUE why is it
printing FALSE?

Error 2
I have 2067 lines in "Installs For 2004" that I need compared to "Completed
Installs" which only has 1690 lines. Once the formula hits 1691 in "Installs
For 2004" I get and error of #VALUE.

I'm telling it the range I need it to look but it looks like it is trying to
match the same line number up in both errors and that is not what I'm trying
to do. I'm just trying to compare what installs I have for the year that
needs to be done to what installs are completed where column A contains the
same value for each install such as a Install Number.

Any help would be greatly appreciated.

Thank you,

Jim