I need to know how to link two workbooks when the name of one of them will be
constantly changing. One workbook will be used to track productivity and
provide some analysis while the other workbook, the one with the changing
name, will be payroll file that lists how many hours each department worked,
wages, OT, etc.

So I will name the data file for the last day of the week and have files
named 06JAN2007, 13JAN2007, etc. So instead of just hitting the = sign and
linking to two workbooks I want it to look for the name of the workbook in a
cell somewhere so that all I have to do each week is change the file name in
the reference cell.

Any idea on how to ask it to get the info from a file that is named in a
cell on the current worksheet?

Hi,

Here are four possible solutions:

1. Indirect Function

With this function you can create references to *open* workbooks and link
them dynamically. The drawback is that the workbook have to be open to link.
and refresh. (This can be over come with a auto open macro that also open the
indirectly linked work sheets by using a datatable and opening the referenced
links.)

2. Direct Links

Using direct links and writing an auto open macro to automatically find and
replace the references to the old workbook and replace with the new workbook.
This method relies on a lookup table recording the current workbook name
(static text updated when the macro is run) and the expected workbook nmae a
formula working out the new name.

3. Basically the same as 2 above but using defined names and then changing
the named ranges presumably using an auto open function.

4. Find and Replace

The tried and tested method of Ctrl+H.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Michael in Texas" wrote:

I need to know how to link two workbooks when the name of one of them will be
constantly changing. One workbook will be used to track productivity and
provide some analysis while the other workbook, the one with the changing
name, will be payroll file that lists how many hours each department worked,
wages, OT, etc.

So I will name the data file for the last day of the week and have files
named 06JAN2007, 13JAN2007, etc. So instead of just hitting the = sign and
linking to two workbooks I want it to look for the name of the workbook in a
cell somewhere so that all I have to do each week is change the file name in
the reference cell.

Any idea on how to ask it to get the info from a file that is named in a
cell on the current worksheet?