formula finds values in apparently blank cells

[Daniel Collison]

Two individuals are completing the same data entry on two separate
spreadsheets. The data entry consists of values in two fields. The
following formula on each spreadsheet concatenates values in the two fields
into a single value in one field: =CONCATENATE(S6, R6)

I want to copy the concatenated values from each spreadsheet into a separate
spreadsheet. When I paste the values into the new spreadsheet, I paste them
as “valuesâ€. I then set up a formula that returns “true†if the values in
both columns match: =AND(B26=H26) The formula works fine.

However, I want to attach an “IF†function to the formula that would return
a blank (“â€) if the values in the two cells are blank. I created the
following formula: =IF(AND(ISBLANK(B26),ISBLANK(H26)),"",AND(B26=H26))

The formula does not recognize apparently blank cells. When I run “evaluate
formulaâ€, Excel recognizes a double quotation mark (“â€) in one of the cells
and evaluates the formula as false. In the second cell, Excel recognizes a
zero (0) and evaluates the formula as true.

Both values are copied/pasted as “values†from the original two
spreadsheets. Both cells are apparently empty. When each cell is
highlighted, the formula bar shows the cell as blank.

Any thoughts on why the formula is reading values in a blank cell?

 

[Yanick]

I tried your formula =IF(AND(ISBLANK(B26),ISBLANK(H26)),"",AND(B26=H26))

It is working exactly you explainded. It does not seems to see the cell as
blank even after a paste as value. Something strange, if you double-click in
both supose to be blank cells, your formula will then work...hummm.

Well try this, it should work fine.
=IF(AND(B26="",H26=""),"",AND(B26=H26))

Hope it will help.