Saturday, March 10, 2012

What is the difference in doing vlookup between MS Excel 2003 and 2007?

Office workstation recently upgraded to MS Office 2007. Spreadsheets that were previously able to perform vlookup now displays an error message "Invalid Reference. This file version cannot contain formulas that reference cells beyond a worksheet size of 256 columns (column IW or higher) or 65536 rows."What is the difference in doing vlookup between MS Excel 2003 and 2007?
Silly Excel 2007. I actually uninstalled it on my home computer and switched back to 2003. Microsoft likes to take you from expert to newbie for very slight gains in functionality...just to sell new product. Argh.



Anyway, it relates to the fact that Office 2007 file don't have the previous column and line limits of 2003. Save the file as the new Excel format, and open it as the "new" file. If that doesn't help, change broad ranges into specific ranges (i.e., A:A, change to A1:A1000). I encountered this situation, and I forget which of those two actually fixed the problem.What is the difference in doing vlookup between MS Excel 2003 and 2007?
Open the Excel files.

Save them as .xlsx files.

Don't save them as .xls files.



Excel 2003 had some limits imposed that were greatly changed in 2007.

However, for Backwards Compatibly, 2007 will re-impose those limits if the files are saved as .xls (2003 format).



Remember, 2003 can only access 65536 rows.

That means A:A means A1:A65536.

2007 can access much more.

Therefore A:A on 2007 means A1:A1048576



Saving a file with such formulas in a 2003 format, will result in your error.
  • smokey bones
  • happy wheels 2
  • No comments:

    Post a Comment