Hi!

I have built a pretty big library of Excel workbooks that are all related.
They all follow a pretty similar format and are all linked back to one
primary workbook that drives the primary data used in all the other
workbooks (builders, projects, start dates, current reporting date, etc.).

The problem I have is no one seems to understand that you have to have all
the workbooks open to rename a tab (if the project were to end and a new one
was created in it's place) and we then get errors all over the place and I
have to spend a bunch of time rebuilding all the links. It also takes
forever to open all of these workbooks at one time just to rename a tab and
keep the links active (probably close to 30MB total)

Is there a way to programmatically create links based on the worksheet
number rather than the tab name to keep the links regardless of what I name
them? That would save me a ton of time and a lot of headaches.

--
Remove 'spam' from email address to contact me directly

Hi Karl,

Is there a way to programmatically create links based on the worksheet
number rather than the tab name

It can be done, but you get a very nice present when done: your new
chore will be to accommodate for users changing the order of your
sheets.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Karl Burrows wrote:
Is there a way to programmatically create links based on the worksheet
number rather than the tab name to keep the links regardless of what I name
them?

Not that I can think of, unless....

If you ensure that the primary workbook is open when any of the derived
workbooks are in use then you could use the INDIRECT function together with a
VBA function which returns the Nth sheet name from the primary workbook.


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

Karl,
If I understand your problem, it is the links going BACK to the primary
workbook which get broken, as opposed to the links which provide data from
the primary workbook to the satellite?

Two ideas come to mind:

Provide automated tools for users to Kill or Build a new project in the
satellite files. This would hide the old project sheet without deleting for
a Kill function without screwing up links, or create a new sheet and
establish the correct links for a Build function.

and/or

For each satellite file, have an on-board, hidden "data record" sheet, that
would hold all the data for all of the individual projects which has to be
linked back to the primary project file. You would then have a standard
interface back to the primary which could be linked or queried by VBA at the
primary. At the satellite, it is easy enough to adapt to changing project or
sheet names without a lot of lost links.

I have used similar techniques to these on my distributed projects
management system in Excel.

Any value to this approach for you?

Alex J


"Karl Burrows" wrote in message
...
Hi!

I have built a pretty big library of Excel workbooks that are all related.
They all follow a pretty similar format and are all linked back to one
primary workbook that drives the primary data used in all the other
workbooks (builders, projects, start dates, current reporting date, etc.).

The problem I have is no one seems to understand that you have to have all
the workbooks open to rename a tab (if the project were to end and a new
one
was created in it's place) and we then get errors all over the place and I
have to spend a bunch of time rebuilding all the links. It also takes
forever to open all of these workbooks at one time just to rename a tab
and
keep the links active (probably close to 30MB total)

Is there a way to programmatically create links based on the worksheet
number rather than the tab name to keep the links regardless of what I
name
them? That would save me a ton of time and a lot of headaches.

--
Remove 'spam' from email address to contact me directly

It sounds like you need to re-assess whether linked cell formulas is
the best way to share information between locations.

--

"Karl Burrows" wrote in message .. .
Hi!

I have built a pretty big library of Excel workbooks that are all related.
They all follow a pretty similar format and are all linked back to one
primary workbook that drives the primary data used in all the other
workbooks (builders, projects, start dates, current reporting date, etc.).

The problem I have is no one seems to understand that you have to have all
the workbooks open to rename a tab (if the project were to end and a new one
was created in it's place) and we then get errors all over the place and I
have to spend a bunch of time rebuilding all the links. It also takes
forever to open all of these workbooks at one time just to rename a tab and
keep the links active (probably close to 30MB total)

Is there a way to programmatically create links based on the worksheet
number rather than the tab name to keep the links regardless of what I name
them? That would save me a ton of time and a lot of headaches.

I thought about this option since I do have a summary page in each workbook
that pulls all the tab information. If linked to the summary instead of
each tab, that would eliminate the problem of breaking the links, but there
is so much data that is linked, I am worried it would greatly increase the
file sizes.

"Alex@JPCS" wrote in message
...
Karl,
If I understand your problem, it is the links going BACK to the primary
workbook which get broken, as opposed to the links which provide data from
the primary workbook to the satellite?

Two ideas come to mind:

Provide automated tools for users to Kill or Build a new project in the
satellite files. This would hide the old project sheet without deleting for
a Kill function without screwing up links, or create a new sheet and
establish the correct links for a Build function.

and/or

For each satellite file, have an on-board, hidden "data record" sheet, that
would hold all the data for all of the individual projects which has to be
linked back to the primary project file. You would then have a standard
interface back to the primary which could be linked or queried by VBA at the
primary. At the satellite, it is easy enough to adapt to changing project or
sheet names without a lot of lost links.

I have used similar techniques to these on my distributed projects
management system in Excel.

Any value to this approach for you?

Alex J


"Karl Burrows" wrote in message
...
Hi!

I have built a pretty big library of Excel workbooks that are all related.
They all follow a pretty similar format and are all linked back to one
primary workbook that drives the primary data used in all the other
workbooks (builders, projects, start dates, current reporting date, etc.).

The problem I have is no one seems to understand that you have to have all
the workbooks open to rename a tab (if the project were to end and a new
one
was created in it's place) and we then get errors all over the place and I
have to spend a bunch of time rebuilding all the links. It also takes
forever to open all of these workbooks at one time just to rename a tab
and
keep the links active (probably close to 30MB total)

Is there a way to programmatically create links based on the worksheet
number rather than the tab name to keep the links regardless of what I
name
them? That would save me a ton of time and a lot of headaches.

--
Remove 'spam' from email address to contact me directly