ERROR problem with Formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pignataro1@cox.net - 10 Dec 2007 15:17 GMT

Hi,
Still having an issue-formula reads:

=IF(AND('Pietra Majella DI'!$I$34<>"",'Pietra Majella Ware'!$I
$34<>""),"ERROR",DI&WARE)

But ERROR is the message I'm getting even though there is only 1 cell
that has info. It should be reading 2-error should only come up if
both Ware and DI cells are filled by mistake.

Anyone have suggestions?

Thanks

Reply to this Message

JE McGimpsey - 10 Dec 2007 15:38 GMT

Are you sure that the cells weren't "cleared" by pressing the spacebar,
in which case, they aren't blank?

Try:

=IF(AND(LEN(TRIM('Pietra Majella DI'!$I$34)) > 0, LEN(TRIM('Pietra
Majella Ware'!$I$34)) > 0), "ERROR", DI&WARE)

In article
<8d5e42a7-a6db-4bc9-8733-c6ba9e3289ad@w56g2000hsf.googlegroups.com>,

> Hi,
> Still having an issue-formula reads:
[quoted text clipped - 9 lines]
>
> Thanks

Reply to this Message

pignataro1@cox.net - 10 Dec 2007 16:01 GMT

> Are you sure that the cells weren't "cleared" by pressing the spacebar,
> in which case, they aren't blank?
[quoted text clipped - 23 lines]
>
> - Show quoted text -

Yes, I have to clear using spacebar and 34 is merged with 35-are these
the problems?

Thanks

Reply to this Message

JE McGimpsey - 10 Dec 2007 16:16 GMT

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-8dd7357d2066@n20g2000hsh.googlegroups.com>,

> Yes, I have to clear using spacebar and 34 is merged with 35-are these
> the problems?

Reply to this Message

pignataro1@cox.net - 10 Dec 2007 16:25 GMT

> Yes. The spacebar doesn't "clear" anything - it inserts space characters
> in the cell.
[quoted text clipped - 12 lines]
>
> - 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

Reply to this Message

Niek Otten - 10 Dec 2007 16:37 GMT

What formula and what values of the input cells gave you #NAME?

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| > Yes. The spacebar doesn't "clear" anything - it inserts space characters
| > in the cell.
[quoted text clipped - 17 lines]
|
| Thanks

Reply to this Message

pignataro1@cox.net - 10 Dec 2007 16:54 GMT

> What formula and what values of the input cells gave you #NAME?
>
[quoted text clipped - 27 lines]
> |
> | 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

Reply to this Message

Niek Otten - 11 Dec 2007 12:22 GMT

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)

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| > What formula and what values of the input cells gave you #NAME?
| >
[quoted text clipped - 40 lines]
|
| Thanks for the help

Reply to this Message

pignataro1@cox.net - 11 Dec 2007 14:07 GMT

> No idea what you mean with "DI&WARE", but it certainly doesn't give you I34 of any of the sheets.
>
[quoted text clipped - 53 lines]
> |
> | 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

Reply to this Message

Niek Otten - 11 Dec 2007 16:55 GMT

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.

Signature

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.
| >
[quoted text clipped - 66 lines]
|
| Thanks

Reply to this Message

pignataro1@cox.net - 11 Dec 2007 17:13 GMT

> 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
[quoted text clipped - 77 lines]
> |
> | Thanks

Thanks for the help-it is working once I cleared the cells out so I
now see the difference. On a seperate question-I have this formula

='VT by Brand 07-09'!R6

I want to go refer back to it in the next cell down but add 6 lines
and go to that cell. So in other words:
='VT by Brand 07-09'!r12 would be the final result. The next line of
data is another 6 lines down. Is there a way to always refer back to
the first cell with the originnal formula and then go to lines or add
6 to get the result of line 12 or 18 or 24? That way when I change
the 1 formula in the 1 cell next month, it will change the entire
sheet?

Thank you for all the assistance

Reply to this Message