Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How is it that a cell can transfer the correct formula, but the w. | Excel Discussion (Misc queries) | |||
Formulas appear in cell instead of formula result | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
Formula window displays correct answer while cell displays incorre | Excel Worksheet Functions |