Formula #Value error
 

I am trying to pull information from another workbook and only get an error
when the other workbook is not open. When the other workbook is open, all is
well.

Here is the formula.
=IF(COUNTIF('[EP01Jan1-2-4-07.xls]Eff'!$AH:$AH,CONCATENATE($C3,$B3,$C$2))=0,"n/a",SUMIF('[EP01Jan1-2-4-07.xls]Eff'!$AH:$AH,CONCATENATE($C3,$B3,$C$2),'[EP01Jan1-2-4-07.xls]Eff'!$Z:$Z)/COUNTIF('[EP01Jan1-2-4-07.xls]Eff'!$AH:$AH,CONCATENATE($C3,$B3,$C$2)))

Looking for any help I cna get on this.
Thanks

Formula #Value error
 

Some functions do not work when they reference closed workbooks.

Given the description of your problem, you are likely encountering this
limitation with one of the functions you are using.
--
Brevity is the soul of wit.


"Hanr3" wrote:

I am trying to pull information from another workbook and only get an error
when the other workbook is not open. When the other workbook is open, all is
well.

Here is the formula.
=IF(COUNTIF('[EP01Jan1-2-4-07.xls]Eff'!$AH:$AH,CONCATENATE($C3,$B3,$C$2))=0,"n/a",SUMIF('[EP01Jan1-2-4-07.xls]Eff'!$AH:$AH,CONCATENATE($C3,$B3,$C$2),'[EP01Jan1-2-4-07.xls]Eff'!$Z:$Z)/COUNTIF('[EP01Jan1-2-4-07.xls]Eff'!$AH:$AH,CONCATENATE($C3,$B3,$C$2)))

Looking for any help I cna get on this.
Thanks

Formula #Value error
 

Could it be the CONCATENATE function that is causing the error?


"Dave F" wrote:

Some functions do not work when they reference closed workbooks.

Given the description of your problem, you are likely encountering this
limitation with one of the functions you are using.
--
Brevity is the soul of wit.


"Hanr3" wrote:

I am trying to pull information from another workbook and only get an error
when the other workbook is not open. When the other workbook is open, all is
well.

Here is the formula.
=IF(COUNTIF('[EP01Jan1-2-4-07.xls]Eff'!$AH:$AH,CONCATENATE($C3,$B3,$C$2))=0,"n/a",SUMIF('[EP01Jan1-2-4-07.xls]Eff'!$AH:$AH,CONCATENATE($C3,$B3,$C$2),'[EP01Jan1-2-4-07.xls]Eff'!$Z:$Z)/COUNTIF('[EP01Jan1-2-4-07.xls]Eff'!$AH:$AH,CONCATENATE($C3,$B3,$C$2)))

Looking for any help I cna get on this.
Thanks

Formula #Value error
 

It doesn't look like CONCATENATE references external workbooks in your
formula. SUMIF and COUNTIF do. It is one or both of those functions.

Dave
--
Brevity is the soul of wit.


"Hanr3" wrote:

Could it be the CONCATENATE function that is causing the error?


"Dave F" wrote:

Some functions do not work when they reference closed workbooks.

Given the description of your problem, you are likely encountering this
limitation with one of the functions you are using.
--
Brevity is the soul of wit.


"Hanr3" wrote:

I am trying to pull information from another workbook and only get an error
when the other workbook is not open. When the other workbook is open, all is
well.

Here is the formula.
=IF(COUNTIF('[EP01Jan1-2-4-07.xls]Eff'!$AH:$AH,CONCATENATE($C3,$B3,$C$2))=0,"n/a",SUMIF('[EP01Jan1-2-4-07.xls]Eff'!$AH:$AH,CONCATENATE($C3,$B3,$C$2),'[EP01Jan1-2-4-07.xls]Eff'!$Z:$Z)/COUNTIF('[EP01Jan1-2-4-07.xls]Eff'!$AH:$AH,CONCATENATE($C3,$B3,$C$2)))

Looking for any help I cna get on this.
Thanks

Formula #Value error
 

Both Countif an Sumif are the cause of the problem. Use Sumproduct in place
of both but you can't use entire columns.

Biff

"Hanr3" wrote in message
...
I am trying to pull information from another workbook and only get an error
when the other workbook is not open. When the other workbook is open, all
is
well.

Here is the formula.
=IF(COUNTIF('[EP01Jan1-2-4-07.xls]Eff'!$AH:$AH,CONCATENATE($C3,$B3,$C$2))=0,"n/a",SUMIF('[EP01Jan1-2-4-07.xls]Eff'!$AH:$AH,CONCATENATE($C3,$B3,$C$2),'[EP01Jan1-2-4-07.xls]Eff'!$Z:$Z)/COUNTIF('[EP01Jan1-2-4-07.xls]Eff'!$AH:$AH,CONCATENATE($C3,$B3,$C$2)))

Looking for any help I cna get on this.
Thanks