Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    Johannesburg
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Building link with filename & path (Office XP)

    Hi

    I need to link to several workbooks in a directory on my HD to one sheet. I have a list of the workbook filenames in a sheet and the path to the directory in one cell. Is it possible to create formulas next to the list of filenames that say link to cell Sheet1!A1 in each of the listed workbooks. I hope my explanation is clear.

    Regards

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Building link with filename & path (Office XP)

    There is a problem: you normally use INDIRECT to refer to a range whose address is given as a string. However, INDIRECT doesn't work with external workbooks unless they're open.
    As a workaround, you can install the free Morefunc add-in from Excel add-ins. It adds a new function INDIRECT.EXT that does support references to external workbooks even if they're closed.
    Say the path name is in H1, and the workbook name in A2. The formula would look like this:
    <code>
    =INDIRECT.EXT("'"&$H$1&"["&A2&"]Sheet1'!A1")
    </code>
    This formula can be filled down.
    Note that the sheet name Sheet1 is fixed. If it can change, you need to specify it in an extra column.

  3. #3
    Lounger
    Join Date
    Apr 2001
    Location
    Johannesburg
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Building link with filename & path (Office XP)

    Thank You Hans

    It works 100%

Posting Permissions

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