DigDB      Excel Add-ins      Add-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes? (more ...)
Features | Testimonial | Download free 15-day trial | Purchase | FAQ | Contact

Excel File Links - Find & Break Broken Links (de-link)

Advanced Excel users sometimes link up several Excel files to form a reporting package. A file link is bascially a cell formula which involves the value of another cell located in another file. For example:
    =[A.xls]Sheet1!$A$1 & "-1"
The 'A.xls' is the file that is linked in. Now, if A.xls is missing, i.e., deleted or renamed, it will cause a broken link error when you open the file with the above forumla (link). There are two challenges in dealing with file links:

  1. Find cells with (broken) file links

    Excel's Links function only tells you what files are linked in. There is no way to find the cells that has file links. Most importanly, you want to locate the cells that has broken links.

  2. Break broken links (de-link)

    When a link is broken, Excel will keep the formula in the affected cells, and even though this forumla can no longer calculate, Excel will keep theirs values the same as their last successfully calculated values. This nutures laziness and harms data integriy, because no matter how many broken links there are, the file will still appear to be functioning. DigDB will allow you to break the broken links by either deleting it or replacing it with an approriate value.

    Large reporting packages often have many broken links. Because large packages take many people and long time to develop. File links exists not only in cells, but also in charts, names, hidden sheets. As people delete, rename, and move files around, broken links become inevitable. And each missing file can affect thousands of cells. It's very tedious to find and break each broken link manually. DigDB is the tool to de-link bad files and clean up.

Demo scenario: in the sample data files below, 'file-with-links.xls' is the file that links to 3 other files, 'A.xls', 'B.xls', 'C.xls'. 'File Links' column are the cells that has formula which links to cells in 'A.xls', 'B.xls', 'C.xls'.
    Find & break broken file links - de-link
    Practice file - file-with-links-demo.xls (16k) - A.xls (16k)

    Now suppose 'B.xls' and 'C.xls' are missing, this will cause broken links. When you open file-with-links.xls, you will see an error prompt. You will need to click 'No' to continue.

    Find & break broken file links - de-link

Find cells with (broken) file links in a sheet, de-link

  1. Follow from the above demo scenario, invoke 'DigDB->File->Find Linked Cells...'

    Find & break broken file links - de-link

  2. Select what to find, and what to do with each cell found.

    Find & break broken file links - de-link

    Find Next Link:

      Click this button to find the next cell that contains a file link. the cell found will be selected in highlight. The box area to the left will display the current cell's address, formula, link status, face value. If the cell's link is not broken, then the 'Go to Source' button will be enabled, and you can click it to open up the source file.

    Search For Broken Link Only:

      If you check this checkbox, the 'Find Next Link' button will search and find only the cells that has broken file links. Cells with good file links will be skipped.

    Replace Link by Face Value:

      De-link the current cell. Replace the current cell's formula by its face value. The cell becomes a value-only cell.

    Replace Broken Link by #REF:

      If you check this checkbox, when you replace a cell by value, if the link is broken, the value will be '#REF', else, it will be its current face value.

    Replace All:

      Same as 'REplace Link by Face Value', but in stead of one at a time, click this button to replace all in the current sheet.

Break Broken Link (de-link)
  1. Follow from the above demo scenario, invoke 'DigDB->File->Break Broken Links...'

    Find & break broken file links - de-link

  2. See the broken links, select how to break them (de-link)

    Find & break broken file links - de-link

    Broken Links:

      The dropdown list has all the missing files that are linked to from the current file. Each missing file is a broken link. And each broken link could affect one or multiple cells in the file. By listing these broken links in a dropdown box, you can select and treat each broken link differently. Or, you can use the 'Break All' button to treat them the same.

    Break Current Broken Link:

      Use the dropdown box to select the broken link (missing file) that you want to deal with. Select how to de-link. Then use this button to break the currently selected broken link.

    Break All:

      Break all the broken links in the dropdown list.

    Replace affected values by their current face values:

      If a cell is affected by the broken link, replace its formula by its current face value. The cell is de-linked and becomes a value-only cell.

    Replace affected values by #REF:

      Replace by #REF or any value you choose.

    Delete affected values:

      Simply delete the forumla in affected cells.

Need more?

Features | Testimonial | Download | Purchase | FAQ | Contact

Copyright © 1999-2007 All rights reserved

DigDB      Excel Add-ins      Add-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes?

Related keywords: Advanced excel tips, excel questions tools, Excel reporting applications, Excel add-ins add-in, Excel shareware, Excel plugin plug-ins plugins, Excel tricks question, Excel utility utilities, Excel add-on add-ons, Excel tip tips, Power excel technique, Excel manipluation, Excel guru, Excel trick, Excel toolkit, Excel app power user

Microsoft Office Marketplace logo
Microsoft and the Office logo are trademarks or registered trademarks of
Microsoft Corporation in the United States and/or other countries.