Arrays referring to other file, cell converts to 'VALUE#!" when opened file ..

Hi..

Required one help..

I am preparing some report called Output.xls where I am using functions like sumif with array linked to cells of different files say Input1.xls etc..These Input files are having very large data base stored in different sheets.

I can get the required answer in Output.xls as long as input data excel file i.e. Input1.xls remain open.

When I open Output.xls alone, the field where I have used functions like sumif got converted to "#VALUE!"

Is there any way by which I can open Output.xls without Opening Input1.xls ..but can get desired output linked to these files... I need to continue output file with formulas.

I cannot copy the entire data in output file in different sheets to generate report...

I am using Excel 2007 version. While closing of output.xls, excel pops with message about saving file in new format. Which format I need to save so as to get desired result without opening input files..

Output.xls when Input.xls is open

Cash Rs. Cheque Rs.
10000 11000
0 0
0 0
1000 2500
3800 4700
0 0

Output.xls when Input.xls is closed

#VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!


Thanks and Regards,

Originally Posted by Glenn Kennedy

A number of functions (including SUMIF) don't work on closed sheets. However, if you can adapt the formula to be a combination of a SUM and an IF formula, it should work out perfectly.

I should have added that SUMPRODUCT also works on closed sheets.

Thanks a lot.. I will try to use SUMPRODUCT..

Thanks a lot.. I will try to use SUMPRODUCT..