I'm a bit nonplussed with some of the discussion on this one so far.
Suppose the 'certain range' is A1:B10 of the active worksheet,
further, suppose the 'certain worksheet' name is 'MySheetName', then I
recorded and simplified to:

Range("A1:B10").Replace What:="#REF!", Replacement:="MySheetName!",
LookAt:=xlPart

which worked and properly fetched data from the other worksheet and
used it in a simple formula.. but have I completely missed something?!