Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

MS Excel--breaking external references

Oct 31, 2005 10:33PM PST

I have created a workbook in MS Excel that has external references (to other .xls files), to track monthly data along with a forecast (as the monthly data changes, the workbook updates). I would like to move a copy of the workbook to a "record" file, and I want "break" those external references so the workbook retains its data. Is there a way to do this easily?

Discussion is locked

- Collapse -
Re: moving Excel file
Nov 1, 2005 12:30AM PST

Pete,

I'm afraid you'll have to move both this spreadsheet and the spreadsheets it takes data from to a new folder. Then if those external references use an absolute address (c:\data\excel\november.xls) you'll have to write some VBA code to cycle through all formulas and change the file location so that it reflects the new location.

If it's a static archive, you can - of course - edit>copy, than edit>paste special>values all worksheets in your spreadsheet. That creates a snapshot, so to say,totally independent of everything else in the world.

Hope this helps.


Kees

- Collapse -
break link
Nov 7, 2005 9:11AM PST

Go to "Edit", "Links" and select "Break Link" to convert formulas that refer to external files to their absolute values.