Linking in Excel

[NoName]

Based on my experience, I have concluded that linking between workbooks is a bad idea. Some people disagree. Are there situations in which you just find links indispensable? I don't think I've ever come across such a situation.

[Pseudolus]

I don't like it, but the philosophy in my office and the way existing reports are structured means that I end up doing it all the time. We do OK, but mistakes happen.

[Gandalf]

What is the alternative?

If it is feasible to incorporate everything into one workbook, that is best.

If not, it is often better to link rather than pasting values from one place to another and having to be sure you really update everything if something changes.

If you are going to "update" by changing the link to use an updated spreadsheet each month, it is safer to link to named ranges than to cell addresses, in case someone changes the structure of the "from" spreadsheet (still not fool-proof or actuary-proof, but it will save you if cells are moved, or if rows / columns are inserted / deleted).

[Fletch]

Can't imagine what life would be like without it. :-?

I've rarely run into problems. Occasionally, I have had are 'phantom' links that hang around after you update to a new workbook (the old file is still listed under 'links' but no cells refer to it). But that never seems to affect any of the calculations.

What problems have other people experienced?

[Cho Da]

Phantom links Insidious circular links Linked sheet off by one cell doubly counted totals Linked sheet not updated path problemsI used to do it a lot. It quickly becomes a maintenance nightmare. Especially if different people are responsible for updating different parts of the "project".

Just say no.

[MountainHawk]

I use the linking between sheets a lot. I'm responsible for maintaining a quarterly report for Pers. Auto that has about 15 exhibits over about 12 files, so I find it very useful to only need to update trend, development, expenses, etc. in one 'input' file, and just link it through all the files.

[E. Blackadder]

I've had to cope with a spreadsheet system that must have had links 5 deep, with no named ranges.

[shudder!]

[Maxine]

At first I thought this was a joke... :-?
I link everything I can (must be the consultant training) and highlight the links by using blue font. Calculating fields in black. Adjustments to calculations in red.

[Dr T Non-Fan]

I use green for links, and blue for values.

I avoid links whenever possible. When I do link, I usually create one sheet that contains exactly what I want to link. This sheet gathers up all the info from the other sheets in the spreadsheet to make for easy linking.

For rate proposals (for example) that are sent to other people, I'd rather C&PSV the information than link them to other sheets.

[MountainHawk]

DTNF -- I agree on that ... I'm hestitant to send anything to people that has formulas in it at all, Paste-Values is excellent for that.

I usually use Blue for values, and use the comments if the links are hard to follow. Usually, though, I just leave 'em like calculated cells. It does warn you when you open a sheet that it contains external links.

[whisper]

Can't imagine what life would be like without it. :-?

I've rarely run into problems. Occasionally, I have had are 'phantom' links that hang around after you update to a new workbook (the old file is still listed under 'links' but no cells refer to it). But that never seems to affect any of the calculations.

What problems have other people experienced?

Phantom links happen a lot when (for some reason) a name from one workbook is "incorporated" into another workbook. If you have a link, but no cells that reference the link, see if you have a named range that does. Delete the name, and the mysterious links will go as well.

[oldgirl]

At first I thought this was a joke... :-?
I link everything I can (must be the consultant training) and highlight the links by using blue font. Calculating fields in black. Adjustments to calculations in red.

I color code my Excel stuff too! However I use blue for data entry and anything calculated and/or linked in black. With linked stuff, I add a comment to the cell or upper left cell of a range.

I also put the name of the range in the cell above the area in bold red font.

Of course all this didn't help when I was working with someone who is color blind! That called for shading.

[NoName]

I probably haven't had as many problems with linking as Cho Da has, but (mostly from seeing other people's spreadsheets, since I have not used links myself in years) have seen a few too many problems with linking to an old version of a spreadsheet (that is still around on the network) and with not updating fully/correctly.

These are user errors which you might think shouldn't be too hard to avoid, but links seem to be conducive to those errors, maybe because psychologically people seem to think everything will work automatically.

MH, it may be that there is a good reason you need to have twelve separate files to prepare 15 exhibits - maybe if you put them together it would be 100MB or something - but often when it comes to file size people will act as though they're working on 10-year-old computers. A 30MB file is better than six 5MB files with links.

Where files really need to be separate, I like DTNF's suggestion, but I take it a little farther and have a sheet for "export" in the source file and one for "import" in the destination file, and C-PS-V the data between them. Admittedly this does add a step to the update, but it seems to be easier to remember that when you want to update a file, the first step is to update the input data, than to deal with linked spreadsheets correctly.

(As far as phantom links, if you still have them once you have cleaned out the named ranges, save the file, go to edit links, and "change source" of all the linked files to be the same as the file you are working on. This seems to force Excel to realize that there really aren't any links left.)

[Mardi]

I use quite a bit of linking. Does anybody know how to paste the values for just the linked values, like you could do in Lotus??? I'd like to keep the other formulas in place when saving historical data.

[MountainHawk]

NoName,

The directory is about 291MB, not including the Access files that store the data. I'm not shy about using space, but I find if I get an excel file much bigger than about 40 MB or so, it becomes a crap shoot whether or not it opens uncorrupted.

[WQN]

I avoid linking but setting up small macros that go and get the data out of another workbook. They only run when I "hit the button" then I know I have updated the values. I prefer to tell Excel what to do than rely on some of its internal functions.

[MountainHawk]

To go one step further, has anyone tried to link from Excel to a Lotus 1-2-3 workbook? It does work sometimes, but I've stopped doing it because I can't figure out when it works and when it doesn't.

[Jack]

You're all a bunch of sheetheads. Did someone say they link 15 sheets? Hasn't anyone heard of a relational databases?

If you're linking 15 sheets you're using Excel as a database. It's not suited for this pupose. Take the initial effort to create a real database you will be rewarded.

[MountainHawk]

No... I link 15 sheets back to one main Excel sheet. It has to be done this way, because I can't use Access to produce the final reports. We distribute these to people electronically, and only certain departments have Access on the machines.

[dummy]

Anybody missing APL days?
Excel is even messier than now good old Lotus 123. I had this problem and ended up storing every data in Access tables, nice for keeping historical updates producing reports by queries etc. You can produce spreadsheet-like forms and program in VBA, avoids having 50 millions sheets for recurrent work.