+ Reply to Thread
Results 1 to 3 of 3

Changing the name of a linked file

  1. #1
    Registered User
    Join Date
    12-07-2018
    Location
    england
    MS-Off Ver
    Offcie 365
    Posts
    2

    Changing the name of a linked file

    I have a file that a number of other excel files are linked to.

    I want to update the name of this excel file.

    However, was wondering if there is a quicker way of getting all of the other files that are linked to update so they are still linked once I change the name. Rather than having to go into each of the linked files and update it manually?

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Changing the name of a linked file

    In a weird sort of things equal to the same thing are equal to each other, try the following.

    Create a "template" macro-enabled file that will act as one of the equal signs. For the purposes of discussion call this File A.xlsm and it will always be kept in the same directory on a common drive that the subordinate files can access.

    For the purposes of example, File B.xlsx will be the file whose name changes daily.

    File A will have a "Control Panel" sheet on it where you can enter the name of File B in a cell. The Control Panel also contains an Excel table that has sheet names and cell names that the subordinate files need to link to.

    Have a macro in File A that opens File B, reads the table on the Control Panel Sheet and populates the cells in File A with the corresponding cell values in File B. Then link all the subordinate files to File A -- ONCE.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Changing the name of a linked file

    It was simple enough to set up an example so I did it. In fact the File A in the attached can be used "for real." Enter in the path name to File B in Cell B2 and File B's name in Cell B3. Fill out the table in columns D:E with the appropriate sheet names and Cell names. Click the button and you are in business.

    Note, File A does not have to be a complete template. All you need to do is create the sheets that are common to File B and the Subordinate Files. You don't need formulas or headers in File A, but headers might make it easier to debug.

    After you click the button, open all the subordinate files and link the cells to the appropriate places in file A. Once you do that, you are done forever. File B updates File A and the subordinate files link to File A.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. changing a linked cell
    By mabowers in forum Excel General
    Replies: 1
    Last Post: 11-20-2014, 02:59 AM
  2. Changing linked cell row reference
    By kta79 in forum Excel General
    Replies: 4
    Last Post: 06-09-2014, 02:01 AM
  3. [SOLVED] sum(offset(... to linked file requires linked file to be open?
    By Oppressed1 in forum Excel General
    Replies: 5
    Last Post: 10-22-2012, 02:21 PM
  4. Replies: 2
    Last Post: 06-25-2009, 02:26 AM
  5. Replies: 0
    Last Post: 06-11-2007, 09:14 AM
  6. [SOLVED] In a linked calculation how do I lose the '0' in the linked file?
    By Anita in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-03-2006, 10:00 AM
  7. Changing Filename when linked to other worksheets
    By Amanda in forum Excel General
    Replies: 1
    Last Post: 10-25-2005, 02:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1