Probably because there are spaces in the other cell.
If you really need spaces to be treated as empty cells, you'll need the kind of test you already had, with TRIM and checking the
length of the string.
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
| > No idea what you mean with "DI&WARE", but it certainly doesn't give you I34 of any of the sheets.
| >
| > For simplicity I use B1 and C1 in this example, and <>"" to test for an empty cell.
| >
| > =IF(B1<>"",IF(C1<>"","ERROR",B1),C1)
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| >
| > | > What formula and what values of the input cells gave you #NAME?
| > | >
| > | > --
| > | > Kind regards,
| > | >
| > | > Niek Otten
| > | > Microsoft MVP - Excel
| >
| > | >
| > | > | > Yes. The spacebar doesn't "clear" anything - it inserts space characters
| > | > | > in the cell.
| > | > | >
| > | > | > And while merged cells are the spawn of the devil, and should never be
| > | > | > used, not even once, they aren't the problem in this case.
| > | > | >
| > | > | > The formula I gave you works with "spaced", merged cells.
| > | > | >
| > | > | > In article
| > | > | > <01ef1b3e-f8b0-45e2-9717-8dd7357d2...@n20g2000hsh.googlegroups.com>,
| > | > | >
| > | > | >
| > | > | >
| > | > | > (e-mail address removed) wrote:
| > | > | > > Yes, I have to clear using spacebar and 34 is merged with 35-are these
| > | > | > > the problems?- Hide quoted text -
| > | > | >
| > | > | > - Show quoted text -
| > | > |
| > | > | Still confused. I'm getting a #NAME? result now. Even though I typed
| > | > | in: 2 cs into 1 of the sheets cell. Any new suggestions?
| > | > |
| > | > | Thanks
| > |
| > | Here is the formula:
| > |
| > | =IF(AND(LEN(TRIM('Pietra Majella DI'!$I$34)) > 0, LEN(TRIM('Pietra
| > | Majella Ware'!$I$34)) > 0), "ERROR", DI&WARE)
| > |
| > | I am trying to have this cell go and look in these 2 seperate sheets
| > | on line 34 and put the numbers and or text in this sheet. if both
| > | sheets DI and Ware have numbers and or text, then I want ERROR to be
| > | displayed but if only1 sheet wheather DI or Ware has info in it, then
| > | that should be displayed in this cell.
| > |
| > | Thanks for the help
|
| Niek,
| This is really close. The only issue is on the DI sheet, when I
| put in 2cs or any other text/numbers, it comes up with ERROR. I would
| like ERROR to show only when both sheets have text and or numbers in
| them. Ware and DI are the names of the sheets I'm referring too.
| Here's the formula:
|
| =IF('Pietra Majella DI'!$I$34<>"",IF('Pietra Majella Ware'!$I
| $34<>"","ERROR",'Pietra Majella DI'!$I$34),'Pietra Majella Ware'!$I
| $34)
|
|
| Thanks
