• Creator
  • #2149714

    Problems with Excel – Linking Workbooks and protecting formulas


    by jeff.w.sheffield ·

    I have a situation where we have two workbooks which populate a third work book. Our users tend to put data in the cells in the wrong location and accidently delete formulas. So I have gone through and locked and unlocked cells and ranges of cells. Then I protected the sheets. but now when i copy the spreadsheets to a new location and try to relink them (Change Source) I get an error message saying that the workbooks can’t be found or the data is encrypted. Any thoughts on how to get around this.

All Answers

  • Author
    • #2912791


      by jeff.w.sheffield ·

      In reply to Problems with Excel – Linking Workbooks and protecting formulas


    • #2912786

      Create and manage links to other workbooks in Excel.

      by Anonymous ·

      In reply to Problems with Excel – Linking Workbooks and protecting formulas

      Microsoft Excel 2000

      A link is a formula that gets data from a cell in another workbook. When you open a workbook that contains links (a linking workbook), Microsoft Excel reads in the latest data from the source workbook or workbooks (updates the links).
      Use links when you want to maintain information in a single workbook, but also use the information in other workbooks. For example, if your product prices change frequently, you might keep a master price list. Other workbooks that use the price data in calculations, such as purchase orders, inventory valuations, or sales estimates, can create links to the price list workbook so calculations always use current prices. When prices change, you only have to enter the new prices in one place.
      When you create a link, Excel creates a formula that includes the name of the source workbook in brackets [ ], followed by the worksheet name, an exclamation point, and the cell reference.
      Create links between workbooks
      1.Open both workbooks.
      2.In the source workbook, select the cells you want to link to and click the Copy button.

      3.Switch to the destination workbook and click the upper left cell of the range where you want the links.
      4.On the Edit menu, click Paste Special, and then click Paste Link.
      You can also create a link starting from the linking workbook. Type an equal sign in a cell, switch to the source workbook, click the cell you want to link to, and then press ENTER.
      You can copy and autofill linking formulas as you do other Excel formulas.
      Putting together a set of linked workbooks
      Before you make extensive use of links, it’s a good idea to do some planning. Here are some tips for creating links.
      Make links easy to track?? There’s no automatic way to find all the cells in a workbook that contain links. You might want to use a particular format or border style for cells containing links, or otherwise document where the links are, so that as your set of linked workbooks grows and gains complexity you’ll be able to maintain it efficiently.
      Turn on automatic calculation ?? Source workbooks that you link to should have automatic calculation turned on (this is the default). To turn automatic calculation on, click Options on the Tools menu, click the Calculation tab, and click Automatic under Calculation.
      Avoid circular links?? Circular links, where two workbooks contain links to each other, can make both workbooks slow to open and update.
      Consider where you’ll store linked workbooks?? If you’re using links to share data on your network, consider where the source and linking workbooks will be stored. Source workbooks stored on a network share are available to other network users, who can store their linking workbooks either on the network or on their own hard disks. Source workbooks on your hard disk, however, are only available to you, so if you put linking workbooks on a network share, other users won’t be able to update links to source workbooks on your system.
      Network drive letters in links
      When you click a cell containing a link and the source workbook is closed, you’ll see the full path to the source workbook. The path uses the drive letter you have mapped to the share. The links continue to work correctly for other users who map different drive letters to the same share.
      You can edit linking formulas to use UNC names, such as \\myserver\myshare, instead of drive letters. UNC names can make links easier to update when several people will use a linking workbook, because Excel can update links that use UNC names even if users don’t have a drive letter mapped to the network share. For example, you can change
      However, using UNC names can also reduce calculation speed, especially if your workbooks use calculation-intensive features such as the Solver add-in program.
      File names and paths in links
      Windows has a 255-character limitation on paths, which also limits the length of the paths you can use when you create links. You’ll need to consider path length in naming files and setting up folder names and structures. In addition, if a workbook file name is longer than 122 characters, you can create links to the workbook but you can’t change the links once created.
      Avoid characters from the Excel linking syntax in file names and worksheet names. You can’t create links to workbooks and worksheets with names that include the characters [ ] and !.
      Linking to cells containing large amounts of data
      Links can only return the first 255 characters in a cell. If you want to link to large amounts of data, first distribute it among several cells on the source worksheet. If you’re linking to a cell with contents that will grow as you maintain the source workbook, keep the 255-character limit in mind.
      Links in shared workbooks
      You can create links to or from shared workbooks, but if one user creates links in a shared workbook while other users have the workbook open, when other users save they may see a #REF! error instead of the linked data. You can avoid this problem by creating all the links before you share the workbook, or at a time when other users aren’t working on it.
      Linking to password protected workbooks
      Assigning a protection password to a source worksheet or workbook does not prevent users of linking workbooks who don’t know the password from updating the links. If you want users to have to enter a password to update links, save the source workbook file with a password to open (click Save As on the File menu, click General Options on the Tools menu).
      Removing links to other workbooks
      If you no longer need a link to information in another workbook, or want to undo a link you just created, you can simply break the link. When you break a link to a another source, all formulas that use that source are automatically converted to their current value. Because the action cannot be undone, it’s a good idea to save a version of your worksheet before you remove links. For more information, see Break links.
      Hope this helps you.

      Please post back if you have anymore problems or questions.

      • #2912613


        by jeff.w.sheffield ·

        In reply to Create and manage links to other workbooks in Excel.

        I may not have been clear on one part of this process. We have a master set of three workbooks. Each week we make changes to employees and equipment, this is in a construction environment. After the changes we copy the masters to a new folder using Windows Explorer. We then rename them to include that week ending date and then open and rebuild the links. This is the point where things go haywire. Thanks for you first reply that is good advice. Will look back at this process.

Viewing 1 reply thread