Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating references
Hi everyone, I need to update every month file references in a master file. The reference files are updated every month with production data and stored in a different folder on a P drive. The folders have the same structure that differs only with the name of the month, so basically I have the following P:\monthly_reporting\2006\1_January\master_product ion.xls, and in February the updated reference files will be in P:\monthly_reporting\2006\2_February\master_produc tion.xls These links need to be updated every month. Is there a way to construct a macro to do that (for the moment, we are updating the references manually with edit/links.)? I am using excel 2003. Thank you!! -- LostInSpace ------------------------------------------------------------------------ LostInSpace's Profile: http://www.excelforum.com/member.php...o&userid=32771 View this thread: http://www.excelforum.com/showthread...hreadid=526032 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating references
If it's suitable if the spreadsheet automatically updetes itself according to
the system date (WATCH OUT: the reference changes automatically on first day of each month), then apply this formula in the cell containing the file reference string: ="P:\monthly_reporting\"&YEAR(TODAY())&"\"&MONTH(T ODAY())&"_"&TEXT(TODAY(),"mmmm")&"\master_producti on.xls" If you want or must update the sheet at a time defined by YOU, use a cell for the month No (in my example A2) and use this formula: ="P:\monthly_reporting\2006\"&A2&"_"&TEXT(DATE(190 0,A2,1),"hhhh")&"\master_production.xls" Reference shall change WHEN YOU overwrite cell A2! Regards, Stefi €˛LostInSpace€¯ ezt Ć*rta: Hi everyone, I need to update every month file references in a master file. The reference files are updated every month with production data and stored in a different folder on a P drive. The folders have the same structure that differs only with the name of the month, so basically I have the following P:\monthly_reporting\2006\1_January\master_product ion.xls, and in February the updated reference files will be in P:\monthly_reporting\2006\2_February\master_produc tion.xls These links need to be updated every month. Is there a way to construct a macro to do that (for the moment, we are updating the references manually with edit/links.)? I am using excel 2003. Thank you!! -- LostInSpace ------------------------------------------------------------------------ LostInSpace's Profile: http://www.excelforum.com/member.php...o&userid=32771 View this thread: http://www.excelforum.com/showthread...hreadid=526032 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating references
Sorry, the correct second formula is
="P:\monthly_reporting\2006\"&A2&"_"&TEXT(DATE(190 0,A2,1),"mmmm")&"\master_production.xls" Stefi €˛LostInSpace€¯ ezt Ć*rta: Hi everyone, I need to update every month file references in a master file. The reference files are updated every month with production data and stored in a different folder on a P drive. The folders have the same structure that differs only with the name of the month, so basically I have the following P:\monthly_reporting\2006\1_January\master_product ion.xls, and in February the updated reference files will be in P:\monthly_reporting\2006\2_February\master_produc tion.xls These links need to be updated every month. Is there a way to construct a macro to do that (for the moment, we are updating the references manually with edit/links.)? I am using excel 2003. Thank you!! -- LostInSpace ------------------------------------------------------------------------ LostInSpace's Profile: http://www.excelforum.com/member.php...o&userid=32771 View this thread: http://www.excelforum.com/showthread...hreadid=526032 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating Absolute References | Excel Worksheet Functions | |||
Updating Name References in Sheet | Excel Programming | |||
Problem updating references | Excel Programming | |||
wraptext is not updating when using references to another cell | Excel Programming | |||
Updating remote references. | Excel Programming |