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

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

HTH


On Sat, 19 Nov 2005 13:32:28 -0600, 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.

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