Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. -- bsmith ------------------------------------------------------------------------ bsmith's Profile: http://www.excelforum.com/member.php...o&userid=14181 View this thread: http://www.excelforum.com/showthread...hreadid=486560 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need assistance linking workbooks by date
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 __ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need assistance linking workbooks by date
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help linking workbooks | Excel Discussion (Misc queries) | |||
linking workbooks | Excel Worksheet Functions | |||
How Do I Get a date reference when linking excel worksheets? | Excel Worksheet Functions | |||
Linking workbooks in 2003 | Excel Worksheet Functions | |||
Linking several workbooks | Excel Discussion (Misc queries) |