External workbook links - external users causing my eyes to twitch

[Lizabeta]

I realize no one can help me with the eye twitching issue... <grin><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
But I'm hoping there is a workaround-the-twitch-causing-user-issue. (Key issue I need help with is in bold at the bottom... the rest of this post is superfluous) <o></o>
<o></o>
So I have a workbook. It links to 30 other workbooks and summarizes how much is left in the various budgets. Each of those workbooks contain a dozen-ish sheets. <o></o>
<o></o>
I know that if I keep the Summary workbook open and the Random Budget workbook open... if I change the name of a tab, it updates automatically. <o></o>
<o></o>
But when I have 300ish other users entering information on 360(ish) worksheets... a certain percentage of them seem to feel the need to change the name of the tabs. <o></o>
<o></o>
Assuming that changing human behavior is not likely: What can I do about my Summary workbook? I get #REF errors every time they decide to change something. (Are they supposed to? No. But that doesn't seem to stop them.) <o></o>
<o></o>
Since the whole point of my Summary workbook is to not open 30ish other workbooks, constantly having to go fix the links is anti-efficient. <o></o>
<o></o>
So I guess I'm looking for some way to retrieve the names of all the worksheets in each workbook... even if those names change. From there I can figure out how to mash together the references to those sheets. <o></o>

 

[GlennUK]

Well, don't let them change the worksheet names. If you protect the workbook structure they won't be able to.

 

[Lizabeta]

Sadly, due to work environment, not a viable solution. (Read: folks higher up than me have 'issues' with that... logical or not, I cannot change that)

 

[xenou]

Maybe you can get help with the eye-twitching, anyway .... But seriously, protecting the sheets from having names changed is really a requirement as 300 users of 360 workbooks is basically already a lot (too much) to manage and worksheet names simply cannot be allowed to change.

The next question might be, why so many worksheets and how does this all work...I'm guessing folks in various departments update "their" workbooks and tabs. But with so many books and sheets I'm not sure there's any easy way to protect yourself from this problem.

You can find the bad links in the edit links dialog. Opening the workbook is about as good a way to see the sheet names as any. You could create a macro to get the names but it would have to open all the workbooks in turn to do that and wouldn't be hugely fast compared to finding the bad link in the edit links dialog.

 

[Asator]

You could write some vba that dumps a list of sheets in another workbook to some worksheet, and use that as the source data for a drop down list. You could then feed the selected worksheet to the modified indirect functions that allow you to reference external workbooks.

Would this be simple? Eh. It wouldn't be pretty though. Much better to lock the structure so that these people aren't breaking data that others rely on.

Google "indirect.ext". It's slow, though, so use it with care.

 

[Lizabeta]

Dumping the names into another workbook was along the lines of what I was thinking. I think I'm going to try that route. As for it taking a long time... I can let that work in the background while I accomplish other tasks rather than use up my time fixing it by hand.

xenou, you are correct that everyone goes in and puts their info in. I'd like to actually put it all into a database that was cleaner, easier to use and 'neater' than the bulky excel thing we've got going on right now... but that would mean two things:

Changing something. (Which. Is. Not. On. with these folks)
And training them (Which is unlikely to go well)

 

[xenou]

Changing something. (Which. Is. Not. On. with these folks)

The story of my (last) job.

 

[Asator]

People hate change