Ron sez:
What happens often is that one person will have one of these spreadsheets open and the other spreadsheets linked to the subject spreadsheet closed and decide to insert a column that throws off the references of the closed linked spreadsheets. If the person knew which spreadsheets were linked to it, the person could open them before inserting the column and avoid throwing off the links.
I’ll bet I don’t have two workbooks with external links. I have a ton with external data queries, but not external links. I don’t have any problem with linking to other workbooks (I don’t think), but I must like my workbooks to be self-contained or something.
It seems the problem is with closed workbooks that contains formulas that link to open workbooks. That is a problem since the open workbook doesn’t know which other workbooks link to it. Ideally, a commandbar that lists all the dependent workbooks and a button that says ‘All’ which opens them up. (Commandbars, or toolbars, or those user interface constructs that existed before the Ribbon).
Is this a problem for everyone? How do you solve it?
Externally linked workbooks can be a real pain for the reason you mention but if you must use them, you can see all the linked workbooks by selecting Edit > Links and you can open each linked workbook one at a time or simply highlight the whole list and select Open Source. This only works if you have access to all the linked workbooks.
A few ways:
1. Use named ranges. However, somebody may still insert a column/row and can mess up some formulae like VLOOKUP, so…
2. Lock and password protect the ranges. If however you don’t want to, for example, it is used for input, then either…
3. Refer other cells to the editable cells, and lock them, or…
4. Lock other cell beyond the range (above or on the side) and lock those to prevent insertion of columns/rows.
If you cannot password protect, then perhaps add an instruction for the kind user to not modify the worksheet….
Like you I have (almost) no workbooks with external links but that is only because I find external queries much more useful. That said, I would think that if I used external links, they would use named ranges. As expected, in a test a few minutes ago, a named range, using absolute references, worked just fine even after I inserted a new column in the source document while the destination workbook was closed.
I use linking when I’m comparing entries in two files: are there missing rows in file A which are available in file B, or when looking up extra data for records, which are in another file. But these are mostly one-offs. When done I throw away the formulas.
I like Tushar’s idea of using named ranges.
I avoid using links into other workbooks, I think it’s asking for trouble! You only need that work book to move, renamed, over written all that good stuff, and your in trouble. That’s not necessarily a problem solved by querying the data in though I guess.
Yeah, I tend to avoid this entirely too in any ‘live’ workbooks. I might reference one that someone else is maintaining in a VBA routine where I need the information, but I keep the location, range info, etc, etc, stored as variables. That way if the other book’s changed I can change my code easily enough to marry up with it. Either that, or if you’ve got a standard header row and the header of the column you need isn’t going to change, how about searching along that row to get the column?
“That is a problem since the open workbook doesn’t know which other workbooks link to it. “
That’s why I wrote ScanXLS.
http://www.sysmod.com/scanxls.htm
Brute force search through all network directories.
This will not catch workbooks on private hard drives (the Rusnak effect)
AFAIK the only way to do that is to use enterprise monitoring software which always runs on login and that monitors the use of every spreadsheet and logs the file accesses.
Vendors like Prodiance, Clusterseven, and what was Compassoft and is now owned by Finsbury. Big money.
I use linked workbooks in one situation. I do reports on a per salesperson basis. These reports rely on common data that is queried from our business system. One refresh, and I can update the data for all the salespeople, and then open the individual spreadsheets.
I definitely use range names that reside on the master spreadsheet. On the report spreadsheet, I use range names that refer to the range names on the master, a double layer of confusion, I suppose.
Second, for any vlookup formulas in this situation, I don’t use a number to refer to column I’m looking at. I’ll have a range name on each column label, and then use column(FirstName) to tell vlookup which column I want returned. This method relies on the first column of the lookup table being ‘A’.
I could add a variable in there that determines which column is the first one (column(FirstName)-column(Left)+1), but so far that hasn’t been necessary as I’m in control of these workbooks.
I don’t consider this an optimal solution, but it matches my brain, and allows me to make structural changes to one report spreadsheet, and just do a bunch of copying of files to get the changes to all the salespeople.
I never use links to other workbooks nowadays (although I did with abandon in my reckless youth!), my way of looking at them is they fall victim to the least controllable aspect of a system: the users. One well-meaning move/delete/rename and the whole house of cards can come tumbling down.
Where multiple workbooks must share data I much prefer a ‘push’ rather than a ‘pull’ approach, feeding data from a collation workbook via VBA. If a opening a linked file fails at this point it is often easier and quicker to log, identify and correct.
Where this is not practical then I use live queries to a database.
I try to avoid links to other workbooks as well, but there are situations where it is the best solution. In those cases, I go the extra mile, using range names for small contained fields and using tables for larger shared data structures. Using column names and row labels and then searching for the row/column of interest solves the potential of someone inserting/deleting rows and columns, but does assume that someone will not be changing the column/row header text. Excel 2007 has much improved tables that provide this capability automatically – too bad I find Excel 2007 too unproductive to use on a routine basis.
GetPivotData has proved to be a useful, fairly safe, mechanism for pulling data from one workbook to another.
–Charlie
Patrick mentions enterprise solutions that continuously monitor the changes to spreadsheets – and comments that they are big money. I can’t comment on the ones he mentions, but ComplyXL DOES all this with an enterprise wide solution (with customers throughout the world) works out at around $10,000 – hardly big money.
Also any solution that needs to actually move spreadsheets to monitor and control them is adding to the problem of broken links.
ComplyXL highlights dependencies so if they change it is obvious – plus the person who is responsible for the spreadsheet can choose to be notified instantly the minute the spreadsheet changes.