VALUE ERRORS WHEN THE FORMULAS ARE CORRECT?!

[ellie0127]

i am trying to link a destination workbook to several different source workbooks. The formula's that I've entered are correct (I've had other people check them to be sure). The problem occurs when I open my workbook and click update values. The numbers then all change to #value. I can get the values to go away if I click edit, links, open source. Once the source is open the value errors go away and the correct information is entered. Why do I have to open the source workbooks and is there a way around this?? PLEASE HELP..

 

[Oaktree]

Sorry, my fault. I forgot that you can't use whole column references.

Try {=SUM(IF('[BLUE SKY.xls]SEPT'!A2:A100='[BLUE SKY.xls]SEPT'!$A$4,'[BLUE SKY.xls]SEPT'!H2:H100))} adjusting the ranges to suit.

 

[ellie0127]

You're very smart... Could you rewrite this one for me??


=COUNTIF('[BLUE SKY.xls]SEPT'!A:A,"R")

 

[Oaktree]

Applying Microsoft's suggestion...

{=SUM(IF('[BLUE SKY.xls]SEPT'!A2:A100="R",1,0))}

 

[ellie0127]

YOU'RE HIRED!! Thanks again.