Linked spreadsheets and Powerpoint Files

[EP]

I have a series of linked spreadsheets I use every year to generate a series
of reports for a client. I roll the model over and resave the folder under an
updated name every year. The links in the underlying spreadsheets as well as
the links from the charts to PowerPoint have to each be updated. Does anyone
know how to roll-over the folder in a way that updates the links as well so
they don't have to be done by hand? It takes me HOURS to do beaus ethere are
45 reports each with 60 pages which have to be updated page by page.

I would be eternally grateful for any help!

 

[Fred Smith]

This should be a problem only if your links include the Folder name. If you
hard code the folder into the link, then you are going to have to change the
name when you change folders.

The likely solution is to remove the folder name. Excel will default to the
folder the file is in. Then when you copy the files to a new folder, they'll
work without change.

Regards,
Fred

 

[EP]

Fred -

That sounds totally reasonable but I am not sure ho to do it. The only way I
know to link is to go through the Edit > Link pull-down commonds which give
you a link box which you use to pick files by their names in entirety. I
can't see (although I am sure it is right under my nose) the option to
removed the folder name. It is tantalizing because sometimes some of the
links to transfer over - but never reliably.

Thank you in advance -

 

[Fred Smith]

You can edit the hyperlink by right-clicking on the cell and choosing Edit
Hyperlink..., You get a dialogue box where you can change the name, or in
this case, delete the folder name, and retain just the file name.

Once you've edited all the file names, save the Excel file.

When you create a new folder, use Windows Explorer to copy the Excel file.
Don't use an Excel Save As... Excel tries to be too helpful, and will modify
your hyperlinks to remember the original folder name, which would negate all
the work.

Try this with a few files names to make sure it works for you. The bad news
is you have to edit all the file names again. The good news is you have to
do it only once more.

Regards,
Fred