I try to design the dependent spreadsheet such that my links are based on
calculations, not just hard-coded cell references to other sheets. If you
can master this, you can prevent the need for what you're trying to do.

So if a cell needs to get a value, I'd have a formula that does a lookup or
a sumproduct on the other sheet. Then as long as certain parameters are
maintained on the source spreadsheet, my formulas will continue to return the
right stuff.

Another thing I do is not use links at all. I use a macro to go get the
data from the source and then just put it in the destination. The nice thing
about this, I have cells that I maintain that contain the path and name of
the source file. So if I want to change the source from say,
GeneralLedgerJan.xls to GeneralLedgerFeb.xls, I just change the value of one
cell and then when I run my macro it opens the right file, gets the data and
then puts it in the right places again. And the cells with the addresses may
or may not even be accessible to the average user so I can protect from
abuse. HTH...

" wrote:

My company has a TON of excel files (like many do). We quite often
use links to previous files (like many do). But as we all know if we
go into the original document and move cell, say C3 to D3. Then a link
that originally referenced cell C3 now references the wrong cell.

My thought of a solution was to program a utility(add-in). The utility
would be ran on every save and would check the currently being saved
file for links. If there was a link it would open up the source file
and create a tab (that will be hidden) and will do nothing more than
store a list of files(and cells) that link to this file. If a user
then tries to edit the spreadsheet and save it they will get a message
warning them about the links that will be affected. And then prompted
to be able to change these links.

Does anyone have a better solution?