Excel relative and absolute reference?

I'm working on a spreadsheet where some of the formulas include data from cells in other Excel documents, in which case I had to write the actual file address into the formula. For example, "=C:\Documents and Settings\Work\Test#Sheet1!A1".


However, if I move the source file elsewhere, the address above does not automatically change to the new address, which causes the data in the previous document to be lost.





Is there a way to write a formula or code that lets Excel automatically change the file address in the formula to the new address when the source file is moved to a new location?





Thanks!!!

Excel relative and absolute reference?
Don't think so. How would Excel know that you moved the file? Excel would have to search your whole hard drive looking for the file. And same problem if you rename the file. Excel won't know where to look for the data.
Reply:That is why I try not to link data between workbooks. Someone decides they want to rename a sheet or the workbook or move the file to another folder or server and your linked workbook is ca-ca.