Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula retrieves wrong data
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;MET RIC_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;METRI C_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...o&userid=26172 View this thread: http://www.excelforum.com/showthread...hreadid=394905 |
#2
|
|||
|
|||
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!My Name) -- Jim "Newmoon" wrote in 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;MET RIC_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;METRI C_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...o&userid=26172 | View this thread: http://www.excelforum.com/showthread...hreadid=394905 | |
#3
|
|||
|
|||
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 :) :) -- Newmoon ------------------------------------------------------------------------ Newmoon's Profile: http://www.excelforum.com/member.php...o&userid=26172 View this thread: http://www.excelforum.com/showthread...hreadid=394905 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Paste is is copying in formula, but display is wrong. | Excel Worksheet Functions | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) |