@timjs: Here’s a bit more information, from a friend in product support (thanks, Ryan!):
“Most of the issues we see in Excel 2007/2010 deal with an old way of making sure to get everything included in the VLOOKUP. Namely, using the entire column as a range (for example, C:H) instead of where your data actually lies (for example, C1:H5000). In Excel 2003, this wasn’t a big issue because this version had a smaller number of worksheet cells to look through. However, with the increased row and column limits in Excel 2007/2010, this may cause performance issues. In other words, with a formula like =VLOOKUP(B1,C:H,2,FALSE), every time Excel wants to calculate that cell it needs to look through about 6.3 million cells. If you have a VLOOKUP formula in thousands of cells, you will definitely see a performance hit.”
This may not be the root of your issue with pasting, Tim, but I think it’s worth sharing here, so that others can avoid potential performance pitfalls when working with VLOOKUP. Without seeing your workbook, it's hard to troubleshoot. Again, I'd send you to MS Answers and also refer you to these four excellent articles:
Excel 2010 Performance: Performance and Limit Improvements
msdn.microsoft.com/.../ff700514.aspx
Excel 2010 Performance: Tips for Optimizing Performance Obstructions (see the Lookups section)
msdn.microsoft.com/.../ff726673.aspx
Excel 2010 Performance: Improving Calculation Performance
msdn.microsoft.com/.../ff700515.aspx
Improving Performance in Excel 2007
msdn.microsoft.com/.../aa730921(v=office.12).aspx
Hope it helps.
- Anneliese
No comments:
Post a Comment