Linked Workbooks (Excel XP)

Topic

New Reply

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.

Reply | Quote

Replies

You can use the INDIRECT function for this:

=INDIRECT("'["&F5&"]Sheet1'!$B4")

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.

Reply | Quote

You can use the INDIRECT function for this:

=INDIRECT("'["&F5&"]Sheet1'!$B4")

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.

Reply | Quote

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.
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).

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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.
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).

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote