There are some functions that will not work on closed workbooks.
A simple workaround is to copy the source sheet in the destination workbook, and make simple links between source and destination cells. ( in case you don't know this method: To copy sheets, with both workbooks open, right click the source sheet tab, select the destination workbook, check the make a copy option, enter.) Then make links from this copy to source, to get all the needed data. Refer your formulas to this copy of your source sheet, you will never get that type of errors... Whenever you open this destination workbook, the links will automatically be updated and you will get the latest data without opening the source. (if the settings are set to update automatically at open, in options excel, and in edit--> links--> startup prompt--excel 2003, or from data-->edit links--> startup prompt)