formula finds values in apparently blank cells
 

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?

formula finds values in apparently blank cells
 

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.
--
Yanick


"Daniel Collison" wrote:

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?