So what happens if you copy the worksheet from file 1 to file 2, then close BOTH files and re-open file 2? Excel should prompt you to help it find the references since file 1 is no longer open.
This shouldn't be any different than the "Change Source" method mentioned earlier. But then again I don't understand what the problem is, so maybe this will uncover a helpful hint.
