removing links in Excel without losing formulas

April 1, 2013 2:28 PM

I have a pretty large spreadsheet in Excel which I use to combine information from everyone in my department for monthly statistics. They all fill out the same spreadsheet, then I have links from all those to mine, with formulas adding everything up. I want to remove the links to one file while leaving the rest, without losing the formulas that contains the links.

I have just lost a person. Instead of being replaced, her duties were shared among everyone else here.

I would like to remove all the references to her file *without* converting the containing formula to a value. Then I will be able to save this nice, cleaner spreadsheet to a template that I can thereafter use every month.

Everything I am finding says "sure you can break a link, we'll just give you the value instead!" No, I just want to delete links to that file from the formula, while leaving the links to the other files, which I still need to be live and updating.

I can't be the only person who wants to do this. And I'd really rather not do it manually. There must be at least 200 separate references.

I am using Excel 2010.

posted by timepiece to Computers & Internet (1 answer total)

You can do a find and replace across all formulas within the workbook. I'm assuming your formulas are something like this:

='[File 1.xlsm]Sheet 1'!$A$1 + '[File 2.xlsm]Sheet 2'!$A$1

Use the Find and Replace feature (ctrl + H) to find the sheet you don't want anymore and replace it with blank.

Find: '[File 1.xlsm]Sheet 1'!$A$1 +
Replace:

Just make sure you select look in formulas under the Find and Replace dialog options.

I'm using Excel 2007, but I believe this will work for 2010 as well.
posted by yodangson at 2:56 PM on April 1, 2013 [2 favorites]