Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#REF!, TRUE or FALSE
Hi everybody Im working on a worksheet where i wanna check cells in two other types of work sheets but but only one of the exists for each number. Ive made this cell: "=OR(SUM(INDIRECT("'[Kreditor mappe "&B9&".xls]Løndebitorer'!$L:$L"))+SUM(INDIRECT("'[Kreditor mappe "&B9&".xls]Ferieval.'!$L$1:$L$40"))0;SUM(INDIRECT("'[Debitor mappe "&B9&".xls]Løndebitorer'!$L:$L"))+SUM(INDIRECT("'[Debitor mappe "&B9&".xls]Ferieval.'!$L$1:$L$40"))0)" but of course i get #REF! because one of the files are missing, i want to know how i can set #REF!=0 and even if i didnt have that problem i would still only get a true or false answer. And i want a 1 or 0 answer. I cant figure out how to get "=if()" to recognize if the "=or()" result is right. I really hope theres a hardcore excel user out there that can help me -- Hurtige ------------------------------------------------------------------------ Hurtige's Profile: http://www.excelforum.com/member.php...o&userid=37381 View this thread: http://www.excelforum.com/showthread...hreadid=570694 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#REF!, TRUE or FALSE
Hurtige,
Generally, you need to wrap at least one indirect in the iserror function to trap the error.. Perhaps: =IF(ISERROR(INDIRECT("'[Kreditor mappe "&B9&".xls]Løndebitorer'!$L:$L")),SUM(INDIRECT("'[Kreditor mappe "&B9&".xls]Ferieval.'!$L$1:$L$40")),SUM(INDIRECT("'[Kreditor mappe "&B9&".xls]Løndebitorer'!$L:$L"))) HTH, Bernie MS Excel MVP "Hurtige" wrote in message ... Hi everybody Im working on a worksheet where i wanna check cells in two other types of work sheets but but only one of the exists for each number. Ive made this cell: "=OR(SUM(INDIRECT("'[Kreditor mappe "&B9&".xls]Løndebitorer'!$L:$L"))+SUM(INDIRECT("'[Kreditor mappe "&B9&".xls]Ferieval.'!$L$1:$L$40"))0;SUM(INDIRECT("'[Debitor mappe "&B9&".xls]Løndebitorer'!$L:$L"))+SUM(INDIRECT("'[Debitor mappe "&B9&".xls]Ferieval.'!$L$1:$L$40"))0)" but of course i get #REF! because one of the files are missing, i want to know how i can set #REF!=0 and even if i didnt have that problem i would still only get a true or false answer. And i want a 1 or 0 answer. I cant figure out how to get "=if()" to recognize if the "=or()" result is right. I really hope theres a hardcore excel user out there that can help me -- Hurtige ------------------------------------------------------------------------ Hurtige's Profile: http://www.excelforum.com/member.php...o&userid=37381 View this thread: http://www.excelforum.com/showthread...hreadid=570694 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop renaming or moving sheet tabs | Excel Discussion (Misc queries) | |||
Disable CUT via toolbars or shortcut keys | Excel Discussion (Misc queries) | |||
Disable "Cut" only | Excel Discussion (Misc queries) | |||
Help: runtime error - Method seriescollection object_chart failed | Charts and Charting in Excel | |||
Grand Totals @ Same Place | Excel Worksheet Functions |