Results 1 to 3 of 3
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    New York, New York, USA
    Thanked 0 Times in 0 Posts

    Linked Workbooks (Excel XP)

    Hi All. I have a number of structurally identical workbooks that contain different data. I also have a master workbook that extracts information from these other workbooks. What I would like to do is have a link into the identical workbooks that is based on a formula - or a formula that controls which file is linked. Can this be done? If so, how?

    I've attached a zip file that contains 3 data files and 1 master file. By changing cell F5 in the master, the formula in cell F8 references one of the data files.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Linked Workbooks (Excel XP)

    You can use the INDIRECT function for this:<pre>=INDIRECT("'["&F5&"]Sheet1'!$B4")</pre>

    but INDIRECT has the disadvantage that it returns #REF if the workbook the formula refers to is closed; it only works correctly if the workbook is open in Excel.

    There is a powerful free add-in function library MoreFunc that contains (among others) a function INDIRECT.EXT that works with closed workbooks.

  3. #3
    Super Moderator macropod's Avatar
    Join Date
    May 2002
    Canberra, Australian Capital Territory, Australia
    Thanked 470 Times in 387 Posts

    Re: Linked Workbooks (Excel XP)

    Hi Tim,

    One way of doing this would be to create a series of intermediate worksheets in your target workbook, each one of which is linked to one of the source workbooks. Then add another worksheet to the target workbook that uses the INDIRECT formula to point to the intermediate worksheets in your target workbook. You can then hide the intermediate worksheets. Because the INDIRECT formula will now only be referring to other worksheets withing the target workbook, it will work correctly when the source workbooks are closed.

    The advantages of this approach are that:
    a) You don't need to use an add-in. This could be important if you want to share the workbook with others - functions/formulae that use add-ins don't work on PCs that don't have the add-ins installed.
    [img]/forums/images/smilies/cool.gif[/img] Becasue all of the source data is captured within the target workbook, you can still reference the source data even if the target workbook is moved to another PC that lacks access to the source workbooks (provided you don't try to update the links).


    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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