#REF!, TRUE or FALSE

[Hurtige]

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: "=IF(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);1;0)" but of course i get #REF! because one of the files are missing, i want to know how i can set #REF!=0.

I really hope theres a hardcore excel user out there that can help me

-Hurtige

EDIT: changed the cell function.

[Bernie Deitrick]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>