ERROR problem with Formula
 

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

ERROR problem with Formula
 

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
,
wrote:

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

ERROR problem with Formula
 

On Dec 10, 10:38 am, JE McGimpsey wrote:
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
,



wrote:
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- Hide quoted text -

- Show quoted text -

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

Thanks

ERROR problem with Formula
 

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
,
wrote:

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

ERROR problem with Formula
 

On Dec 10, 11:16 am, JE McGimpsey wrote:
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
,



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

ERROR problem with Formula
 

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


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message ...
| On Dec 10, 11:16 am, JE McGimpsey wrote:
| 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
| ,
|
|
|
| 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

ERROR problem with Formula
 

On Dec 10, 11:37 am, "Niek Otten" wrote:
What formula and what values of the input cells gave you #NAME?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in ...

| On Dec 10, 11:16 am, JE McGimpsey wrote:
| 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
| ,
|
|
|
| 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

ERROR problem with Formula
 

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

wrote in message ...
| On Dec 10, 11:37 am, "Niek Otten" wrote:
| What formula and what values of the input cells gave you #NAME?
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| wrote in ...
|
| | On Dec 10, 11:16 am, JE McGimpsey wrote:
| | 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
| | ,
| |
| |
| |
| | 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

ERROR problem with Formula
 

On Dec 11, 7:22 am, "Niek Otten" wrote:
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

wrote in ...

| On Dec 10, 11:37 am, "Niek Otten" wrote:
| What formula and what values of the input cells gave you #NAME?
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
| | wrote in ...

|
| | On Dec 10, 11:16 am, JE McGimpsey wrote:
| | 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
| | ,
| |
| |
| |
| | 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

ERROR problem with Formula
 

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

wrote in message ...
| On Dec 11, 7:22 am, "Niek Otten" wrote:
| 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
|
| wrote in ...
|
| | On Dec 10, 11:37 am, "Niek Otten" wrote:
| | What formula and what values of the input cells gave you #NAME?
| |
| | --
| | Kind regards,
| |
| | Niek Otten
| | Microsoft MVP - Excel
| | | wrote in ...
|
| |
| | | On Dec 10, 11:16 am, JE McGimpsey wrote:
| | | 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
| | | ,
| | |
| | |
| | |
| | | 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

ERROR problem with Formula
 

On Dec 11, 11:55 am, "Niek Otten" wrote:
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

wrote in ...

| On Dec 11, 7:22 am, "Niek Otten" wrote:
| 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
| | wrote in ...

|
| | On Dec 10, 11:37 am, "Niek Otten" wrote:
| | What formula and what values of the input cells gave you #NAME?
| |
| | --
| | Kind regards,
| |
| | Niek Otten
| | Microsoft MVP - Excel
| | | wrote in ...
|
| |
| | | On Dec 10, 11:16 am, JE McGimpsey wrote:
| | | 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
| | | ,
| | |
| | |
| | |
| | | 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

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