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_production.xls, and in February the updated reference files will be in P:\monthly_reporting\2006\2_February\master_production.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!!

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(TODAY())&"_"&TEXT(TODAY(),"mmmm")&"\master_production.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(1900,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_production.xls,
> and in February the updated reference files will be in
> P:\monthly_reporting\2006\2_February\master_production.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
>
>

Sorry, the correct second formula is

="P:\monthly_reporting\2006\"&A2&"_"&TEXT(DATE(1900,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_production.xls,
> and in February the updated reference files will be in
> P:\monthly_reporting\2006\2_February\master_production.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
>
>