Need assistance linking workbooks by date

I am having trouble with a workbook that gathers info from seperate workbooks that are created and saved by date on a daily basis.
The main workbook gathers data into cells from external workbooks that are saved by date. I am having to manually go in and change the file name (date in name) within each cell to calculate the info that I need.
Is there an easier way or macro to change the date to match the workbook that the info is in? I think that I have done this before but can not remember how and everythig that I have tried is not working.

Here is an example of the problem:
Main workbook: DATA.xls
Pulls from: INFO Nov 19.xls, MATERIAL Nov 19.xls

Here is the formula that is used to pull the info (names changed):
=SUM('\\folder1\public\info tracker\November 05\ORDERS C SHIFT\[NOV 19 CP.xls]Tracker'!$Q$9)

The next day (or for previous days) it will need to pull, INFO Nov 20, MATERIAL Nov 20, etc.. The main workbook does not change and is used only for gathering info from other sources.

Thank you.

Have you tried EDIT Links, Change Source, and selecting the Nov20
file... etc?

HTH


On Sat, 19 Nov 2005 13:32:28 -0600, bsmith
<[email protected]> wrote:

>
>I am having trouble with a workbook that gathers info from seperate
>workbooks that are created and saved by date on a daily basis.
>The main workbook gathers data into cells from external workbooks that
>are saved by date. I am having to manually go in and change the file
>name (date in name) within each cell to calculate the info that I
>need.
>Is there an easier way or macro to change the date to match the
>workbook that the info is in? I think that I have done this before but
>can not remember how and everythig that I have tried is not working.
>
>Here is an example of the problem:
>Main workbook: DATA.xls
>Pulls from: INFO Nov 19.xls, MATERIAL Nov 19.xls
>
>Here is the formula that is used to pull the info (names changed):
>=SUM('\\folder1\public\info tracker\November 05\ORDERS C SHIFT\[NOV 19
>CP.xls]Tracker'!$Q$9)
>
>The next day (or for previous days) it will need to pull, INFO Nov 20,
>MATERIAL Nov 20, etc.. The main workbook does not change and is used
>only for gathering info from other sources.
>
>Thank you.

RB
__

From the main workbook, Edit > Links. For each linked file, select the file,
then Change Source and point to the new day's data. Alternately, you could
do a global find / replace of the old date with the new date, looking in
formulas.

"bsmith" wrote:

>
> I am having trouble with a workbook that gathers info from seperate
> workbooks that are created and saved by date on a daily basis.
> The main workbook gathers data into cells from external workbooks that
> are saved by date. I am having to manually go in and change the file
> name (date in name) within each cell to calculate the info that I
> need.
> Is there an easier way or macro to change the date to match the
> workbook that the info is in? I think that I have done this before but
> can not remember how and everythig that I have tried is not working.
>
> Here is an example of the problem:
> Main workbook: DATA.xls
> Pulls from: INFO Nov 19.xls, MATERIAL Nov 19.xls
>
> Here is the formula that is used to pull the info (names changed):
> =SUM('\\folder1\public\info tracker\November 05\ORDERS C SHIFT\[NOV 19
> CP.xls]Tracker'!$Q$9)
>
> The next day (or for previous days) it will need to pull, INFO Nov 20,
> MATERIAL Nov 20, etc.. The main workbook does not change and is used
> only for gathering info from other sources.
>
> Thank you.
>
>
> --
> bsmith
> ------------------------------------------------------------------------
> bsmith's Profile: http://www.excelforum.com/member.php...o&userid=14181
> View this thread: http://www.excelforum.com/showthread...hreadid=486560
>
>