Hello Sushma,
Comapring values is a tricky business. Excel cell formatting makes it more complex.
1) Comparing strings: A space here and there makes all the disfference. You may need to use the trim function to strip all the un-necessary spaces in both the strings.
Thus, to compare string values in two cells (A1 on Sheet1 and A2 on Sheet2) you should be using:
=trim(Sheet1!A1) = trim(Sheet2!A2)
2) Comparing numbers: Here again, due to cell formatting both cells may display same value (1.21) but actual values may be different (1.21 and 1.209). You need to decide the extent of comparison and then use appropriate functions.
As an example something like
=round(Sheet1!A2,2) = round(Sheet2!A2) will result in True.
3) Comparing dates: ditto as numbers.
Ths cells may display 1/1/01 but their actual values might be 1/1/01 10:35 and 1/1/01 10:36 which obviously are not equal.
= And ((Day(Sheet1!A1) = Day(Sheet1!A1)) (Month(Sheet1!A1) = Month(Sheet1!A1)) (Year(Sheet1!A1) = Year(Sheet1!A1))
will however result in True.
Your feedback, as to whether this helped or more assistance is needed, would be highly appreciated.
I would be glad to provide any more specific assistance you may need in this regard.
With best compliments,
SanganakSakha,
Simple And Effective Solutions To Small Problems, No QuickFixes.