Solved

Excel 2007 best practices when linking to an external workbook

Posted on 2011-09-15
7
950 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:gabrielPennyback
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 13

Accepted Solution

by:
nike_golf earned 300 total points
ID: 36546896
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
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 36547019
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
 
LVL 13

Assisted Solution

by:nike_golf
nike_golf earned 300 total points
ID: 36547024
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Assisted Solution

by:nike_golf
nike_golf earned 300 total points
ID: 36547041
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
 
LVL 17

Assisted Solution

by:andrewssd3
andrewssd3 earned 100 total points
ID: 36547939
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
 
LVL 2

Assisted Solution

by:jan24
jan24 earned 100 total points
ID: 36549460
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
 
LVL 1

Author Closing Comment

by:gabrielPennyback
ID: 36552692
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question