Need help from Excel experts
 

I'll try to explain this as simply as possible. Note that I have good basic Excel skills, but far from advanced Excel skills.

Spreadsheet "Report" is in "A" directory. It pulls data from another spreadsheet "Jan. P&L" that is also in directory "A", and several others.

Next month I'll need to duplicate this, but in directory "B". If I just copy Spreadsheet "Report" to directory "B" the links still point to directory "A", right? So if I create a new spreadsheet "Feb P&L" and put it in directory "B", my new spreadsheet "Report" won't see the new data unless I redirect all the links, of which there will be many.

Is there a way to have the main "Report" spreadsheet act as a template where it will look locally for spreadsheet "[current month]P&L" without having to manually change the links? I'd like to be able to automate as much of this as possible.

I believe you can modify the sheet to point to a file named P&L in the same directory. When you copy this to a new directory, I think it will look to the local file first.

Another possibility is to look at the "edit links" option - it should give you a easy method to change the source file.

AFAIK Excel looks for local links first. As long as the files remain in the same folder, it should be OK. Why not test it with two simple spreadheets?

That's generally right AFIK - when you open a spreadsheet with linked cells, it will search for it locally, if it does not find it, it will ask you to locate it so you can point to it manually - and it will update all related links.

You'll need to use VBA to handle the dynamic filename reference (e.g. "[current month]P&L").

http://www.excel-vba.com/

Or the low-tech way is select entire Report spreadsheet and use Edit | Replace to change all the links at once.

Thanks for the suggestions. The edit:links definitely helped. I'd like to get this automated as much as possible, but have the feeling it's going to require too much manipulation of data each month for that to happen.

If you keep the month name in the directory and always use the same filename, it should work fine.

For example, instead of /directoryB/janPL and /directoryC/FebPL use
/January/PL
/February/PL

By keeping the file in the local directory always the same name, Excel will automatically do the links.

Good luck,

David