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?

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?

So you have two workbooks (WkbkA and WkbkB) and wkbkB has formulas that refer
back to wkbkA.

And each time you open wkbkA, you're going to look for links in other
workbooks? If that's what you plan, then it seems like an impossible task to
me.

There can be files on the common network drives that you know about.
There can be files on network drives that you don't know about--or not even have
access to.
There can be files on local disks that you won't have access to. These could be
on the user's C: drive or even removeable disk drives
(CDs/Floppies/Thumbdrives).

And there's no reason that wkbkA knows that there are other files that link to
it. (WkbkA could have links to other workbooks, but that's the opposite
problem--or it may have no links in it at all.)

If your links are simple formulas that refer to a cell or a range of cells, then
you may find that it makes much more sense to give those ranges a nice name.

Then the "receiving" workbooks can refer to that name in their formulas. When
wkbkA (the "sending" workbook) changes location of that name, the receiving
workbooks won't care--because they're using the names.

Yeah, yeah. Fixing this will be a real PITA for all those existing workbooks
with links back to wkbkA.

======
The only other solution I know is to open every(!) workbook that has links to
wkbkA when you're going to make changes to wkbkA that affect your link formulas.



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?

--

Dave Peterson

Dave,

Let me clarify.

Assume two workbooks wkbkA and wkbkB.

wkbkA contains data, and wkbkB is just created. You Decide that wkbkb
Cell C7 should contain a link to wkbkA. You go to Save wkbkB and your
add-in (on save) runs. the macro writes to wkbkA and creates a tab
that is hidden and contains a note that says path\wkbkB.xls'sheet1!
c7 then everything saves and wkbkA is closed (you never see that
wkbkA was open (in fact it shouldnt be opened just directly written
to))... then if someone opens wkbkA and decides to insert a new column
shifting column c to the right and becoming column d this would screw
up your wkbkB if you updated links. to prevent this when the user
goes to save wkbkA the macro runs and reads the hidden tab and will
alert the user that path\wkbkB.xls'sheet1!c7 will be affected. Then it
will prompt the user to cancel the save or to edit the link for them.

Mike,

I like the idea of a macro that writes the source down but thats also
why I like a true link, the link is the source.

You could try <bg.

I think you'd need an application event that looks for links when you're saving
any (and all) workbooks. Chip Pearson has some instructions about using
application events he

http://www.cpearson.com/excel/AppEvent.htm

But it still sounds scary to me.

If I have wkbkA open (happily making changes to the data) and your code tries to
open the file to update it, then you'll have trouble.

If you (or the users) decide that macros shouldn't be enabled--or that events
should be turned off, then there's trouble.

You're not going to like my response, but here goes.

I think one person should be responsible for making layout changes to those data
workbooks. Anyone can update (if you want), but no one can move columns around
or insert/delete columns. (Formulas in those receiving workbooks have to be
more robust, too--like Mike H. suggested.)

Then if that data workbook has to be redesigned, then one person makes the
changes. And updates the other workbooks--or at least informs or helps the
others with their updates. The timing would have to be published, too.

On June 30, 2008, workbookA.xls is changing its format. Existings columns J:K
will now hold xxx and yyy. There will be new data added in columns L:M. There
is a test workbook in zzzzzz that you can use to make sure your files will
retrieve the correct information.

Please contact Spencer Sadkin at xxxx if you have questions or help modifying
your workbooks.



wrote:

Dave,

Let me clarify.

Assume two workbooks wkbkA and wkbkB.

wkbkA contains data, and wkbkB is just created. You Decide that wkbkb
Cell C7 should contain a link to wkbkA. You go to Save wkbkB and your
add-in (on save) runs. the macro writes to wkbkA and creates a tab
that is hidden and contains a note that says path\wkbkB.xls'sheet1!
c7 then everything saves and wkbkA is closed (you never see that
wkbkA was open (in fact it shouldnt be opened just directly written
to))... then if someone opens wkbkA and decides to insert a new column
shifting column c to the right and becoming column d this would screw
up your wkbkB if you updated links. to prevent this when the user
goes to save wkbkA the macro runs and reads the hidden tab and will
alert the user that path\wkbkB.xls'sheet1!c7 will be affected. Then it
will prompt the user to cancel the save or to edit the link for them.

Mike,

I like the idea of a macro that writes the source down but thats also
why I like a true link, the link is the source.

--

Dave Peterson