Excel 2007 best practices when linking to an external workbook

I have a workbook in Excel 2007 in which 8 sheets have up to 1800 formulas linking to an external workbook. Since each sheet has the same formulas in the same cells, I could easily set it up so that one sheet links to the external workbook and let the other 7 sheets link to that sheet.

Would that somehow be more of a "best practices" way to do it? Or does it matter either way?

Thanks,
John
LVL 1
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
 
nike_golfConnect With a Mentor Commented:
This link for Optimizing Excel should help you understand the nuances of Excel 2007.

http://msdn.microsoft.com/en-us/library/aa730921%28v=office.12%29.aspx

NG,
0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Thanks for posting, nike golf. Great link, which I will refer to a lot I think. Do you happen to know if it addresses my particular question?

Thnx,
John
0
 
nike_golfConnect With a Mentor Commented:
A quote from the link posted

"Links Between Workbooks

Avoid inter-workbook links wherever possible: they are slow, easily broken, and not always easy to find and fix.

Using fewer larger workbooks is usually (but not always) better than using many smaller workbooks. Some exceptions to this might be when you have a lot of front-end calculations that are so rarely recalculated that it makes sense to put them in a separate workbook, or when you do not have enough RAM.

Try to use simple direct cell references that work on closed workbooks. By doing this, you can avoid recalculating all your linked workbooks whenever you recalculate any workbook. Also, you can see the values Excel has read from the closed workbook, which is frequently important for debugging and auditing the workbook.

If you cannot avoid using linked workbooks, try to have them all open rather than closed, and open the workbooks that are linked to before you open the workbooks that are linked from.
Links Between Worksheets

Using many worksheets can make your workbook easier to use, but generally it is slower to calculate references to other worksheets than references within worksheets.

In Excel 97 and Excel 2000, worksheets and workbooks are calculated in alphabetical name sequence with individual calculation chains. With these versions, it is very important to name the worksheets in a sequence that matches the flow of calculations between worksheets."


"Large Number of Links to Other Workbooks

If possible, open the workbooks that you are linking to before you open the workbook that contains the links. Often it is faster to open a workbook than to read the links from a closed workbook."
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
nike_golfConnect With a Mentor Commented:
I've found a large number of external links just kills the performance in Excel 2007 much worse than Excel 2003 a big step backwards in my opinion. That said, I would avoid external links if at all possible and incorporate them into a single workbook.

NG,
0
 
andrewssd3Connect With a Mentor Commented:
I agree with all the above, and would advise you to take care with external links - they are slower to update in 2007 and will cripple performance if you need to change the link source when you have more than a few hundred cells.  

Sometimes you must use extrnal links, and the most efficient way I have found is to use an array-entered reference.  Excel views this as just one reference even if it incorporates many cells, and it will be much faster to change the link source.  Obviously you can only do it with a contiguous block of cells:  select the whole block of cells in the target worksheet where you want the data to go, and type = to start the formula.  Then Switch to the source worksheet and highlight a the block of cells you wnat to link to - it must have the same dimensions as the target block.  Then press ctrl-shift-enter to array-enter the formula, and you're done.  The other advantage to this is that you can't inadvertently change one of the cells in the middle of the array.
0
 
jan24Connect With a Mentor Commented:
An alternative to linking is to have the source workbook save data to a CSV or XML file, either on clicking a button, or automatically on save.  The destination workbook can then contain a macro that reads that CSV or XML and puts the values into a predetermined range.  That can improve performance and give you more control over the link, though it does obviously involve writing some macros.
0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Thanks, all of you. I posted a followup question here about how to use XML. Please take a look.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27312533.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.