How to prevent a formula from returning #REF! instead return 0

[Franklin]

Guys,

I am collating reports and to create a summary I have to reference cells in
varoius daily reports from different sites. However if a report is missing
i.e the excel file is missing I get a #REF! value. This messes up all other
analysis I need to do on my data.
How can I get the cells to return the number zero instead of #REF!.

Cheers

 

[Kevin B]

You can use the following generic function:

=IF(ISERROR(Formula),0,formula)

Where formula is the formula that you're currently using that produces the
#Ref error.

 

[Dave Peterson]

If the workbook doesn't exist, I think you'll always get this error.

Maybe you could keep the formulas as text.
Change the leading equal sign to $$$$$=
and change it back to just the equal sign after the workbook is created.

Another option may be to create dummy workbooks. Then use those dummy
workbooks/worksheets in your formulas. When the new workbook is created, use
edit|Links to change the source to the correct workbook.