To my knowledge you have to have the other workbook open to set up the formula in the first place but then it will update without the other worksheet being open.
But if you have heaps of these formulas, its better to have the other worksheet open in the background otherwise to takes to long to update.
Of cause if this is a function you perform often you could always use a macro to open and close the other sheet behide the scenes so to speak.