adjust links to external workbook automatically
 

I have a summary workbook which links to a bunch of cells in an external
workbook. The external workbook's name is "01 04 2007 GDCS forecast & TCAPS
analysis"

However, every time this external workbook is modified, I like to change its
name to reflect the most recent update; i.e., if I were to modify the
workbook today I would change its name to "01 09 2007 GDCS forecast & TCAPS
analysis", etc.

Therefore, I'm wondering if there is a way (a macro, probably?) for the
workbook that links to this workbook to automatically change its formulas to
reflect the new workbook name? Hope that makes sense? I know I can do this
via the Edit--Links menu path but the person using this summary file is not
that expert with Excel, and I would prefer such updates to happen
automatically, if possible.

Dave
--
Brevity is the soul of wit.

adjust links to external workbook automatically
 

A more efficient approach might be to have a "working copy" of the workbook
(named whatever you like) that you SAVE AS a published copy in the proper
folder.

You'd probably want to have a cell in the working copy that indicates the
version and have the dependent workbooks link to that cell, too.

That way no links would need to be changed.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Dave F" wrote:

I have a summary workbook which links to a bunch of cells in an external
workbook. The external workbook's name is "01 04 2007 GDCS forecast & TCAPS
analysis"

However, every time this external workbook is modified, I like to change its
name to reflect the most recent update; i.e., if I were to modify the
workbook today I would change its name to "01 09 2007 GDCS forecast & TCAPS
analysis", etc.

Therefore, I'm wondering if there is a way (a macro, probably?) for the
workbook that links to this workbook to automatically change its formulas to
reflect the new workbook name? Hope that makes sense? I know I can do this
via the Edit--Links menu path but the person using this summary file is not
that expert with Excel, and I would prefer such updates to happen
automatically, if possible.

Dave
--
Brevity is the soul of wit.

adjust links to external workbook automatically
 

That's an interesting suggestion, thanks.
--
Brevity is the soul of wit.


"Ron Coderre" wrote:

A more efficient approach might be to have a "working copy" of the workbook
(named whatever you like) that you SAVE AS a published copy in the proper
folder.

You'd probably want to have a cell in the working copy that indicates the
version and have the dependent workbooks link to that cell, too.

That way no links would need to be changed.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Dave F" wrote:

I have a summary workbook which links to a bunch of cells in an external
workbook. The external workbook's name is "01 04 2007 GDCS forecast & TCAPS
analysis"

However, every time this external workbook is modified, I like to change its
name to reflect the most recent update; i.e., if I were to modify the
workbook today I would change its name to "01 09 2007 GDCS forecast & TCAPS
analysis", etc.

Therefore, I'm wondering if there is a way (a macro, probably?) for the
workbook that links to this workbook to automatically change its formulas to
reflect the new workbook name? Hope that makes sense? I know I can do this
via the Edit--Links menu path but the person using this summary file is not
that expert with Excel, and I would prefer such updates to happen
automatically, if possible.

Dave
--
Brevity is the soul of wit.

adjust links to external workbook automatically
 

All you have to do is have *both* WBs open at the same time!

XL will automatically change all the formulas on the destination sheet to
reflect any name changes to the source sheet at the completion of a "Save
As".
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dave F" wrote in message
...
That's an interesting suggestion, thanks.
--
Brevity is the soul of wit.


"Ron Coderre" wrote:

A more efficient approach might be to have a "working copy" of the
workbook
(named whatever you like) that you SAVE AS a published copy in the proper
folder.

You'd probably want to have a cell in the working copy that indicates the
version and have the dependent workbooks link to that cell, too.

That way no links would need to be changed.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Dave F" wrote:

I have a summary workbook which links to a bunch of cells in an
external
workbook. The external workbook's name is "01 04 2007 GDCS forecast &
TCAPS
analysis"

However, every time this external workbook is modified, I like to
change its
name to reflect the most recent update; i.e., if I were to modify the
workbook today I would change its name to "01 09 2007 GDCS forecast &
TCAPS
analysis", etc.

Therefore, I'm wondering if there is a way (a macro, probably?) for the
workbook that links to this workbook to automatically change its
formulas to
reflect the new workbook name? Hope that makes sense? I know I can do
this
via the Edit--Links menu path but the person using this summary file is
not
that expert with Excel, and I would prefer such updates to happen
automatically, if possible.

Dave
--
Brevity is the soul of wit.