Formula retrieves wrong data

[Newmoon]

hello, world.
i´ve got a workbook that retrieves data from an SQLserver.
on sheet 1 i´ve got a formula that gets data from sheet2 as long a
there´s no more current value on sheet 3.
i´m using this frmula:

Code
-------------------


Code
-------------------
=IF(ISBLANK(METRIC_Kname1_S2);METRIC_Kname1_S1;METRIC_Kname1_S2
-------------------

METRIC_Kname1_Sn are defined names of cells both on sheet 2 and 3.

unfortunately excel accepts the formula but executes it incorrect.

i always retrieve the value from sheet3, regardless of it´s value, even if it´is empty.
the next hting i tried was:

Code
-------------------
=IF(Len(METRIC_Kname1_S2)=0;METRIC_Kname1_S1;METRIC_Kname1_S2
-------------------

the result was the same as with the formula above.

for testing purposes i´ve hardcoded it this way:

Code
-------------------
=IF(ISBLANK(METRIC_Kname1_S2);"the value of sheet2 is the most curent one";"there´s a more current one on sheet3"
-------------------


and it works!!
i become more and more despair of trying to find a formula that works...
is there anybody who could help me to solve the problem?
thanks a lot in advance
Newmoo

 

[Jim Rech]

METRIC_Kname1_Sn are defined names of cells both on sheet 2 and 3

The only way that can be true is if at least one of the names is a "local
name". A local name has, as part of its name, the name of the worksheet it
is defined on:

Global name: "MyName"
Local name on sheet1: "Sheet1!MyName"
Local name on sheet2: "Sheet2!MyName"

If you want a local name returned you have to specify the full name in your
formula, including the sheet name part of it.

=IF(ISBLANK(Sheet1!MyName),Sheet2!MyName,Sheet1!MyName)

--
Jim
message |
| hello, world.
| i´ve got a workbook that retrieves data from an SQLserver.
| on sheet 1 i´ve got a formula that gets data from sheet2 as long as
| there´s no more current value on sheet 3.
| i´m using this frmula:
|
| Code:
| --------------------
|
|
| Code:
| --------------------
| =IF(ISBLANK(METRIC_Kname1_S2);METRIC_Kname1_S1;METRIC_Kname1_S2)
| --------------------
|
| METRIC_Kname1_Sn are defined names of cells both on sheet 2 and 3.
|
| unfortunately excel accepts the formula but executes it incorrect.
|
| i always retrieve the value from sheet3, regardless of it´s value, even
if it´is empty.
| the next hting i tried was:
|
| Code:
| --------------------
| =IF(Len(METRIC_Kname1_S2)=0;METRIC_Kname1_S1;METRIC_Kname1_S2)
| --------------------
|
| the result was the same as with the formula above.
|
| for testing purposes i´ve hardcoded it this way:
|
| Code:
| --------------------
| =IF(ISBLANK(METRIC_Kname1_S2);"the value of sheet2 is the most
curent one";"there´s a more current one on sheet3")
| --------------------
|
|
| and it works!!
| i become more and more despair of trying to find a formula that works...

| is there anybody who could help me to solve the problem?
| thanks a lot in advance
| Newmoon
|
|
| --
| Newmoon
| ------------------------------------------------------------------------
| Newmoon's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=26172
| View this thread: http://www.excelforum.com/showthread.php?threadid=394905
|

 

[Newmoon]

hi, jim. thanks for your response!
after days (!) of trying to fix the problem i found the reason causing
my formula "to raise errors".
the problem appears not within excel or it´s functionality, in fact it
comes out on server side.
but for all that i thought, defined names within a workbook are unique,
so assigning the sheetnames when referencing them is lapsed... ?
ok, thanks again for help