New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

EXCEL - VLOOKUP

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

EXCEL - VLOOKUP

Postby fave » Fri Mar 30, 2012 8:23 am

I am trying to do a VLOOK UP of info between 2 Excel worksheets and it is giving me the message as follows"

" 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".

Bot my worksheets are much smaller than the above so why does the VLOOK UP not work

I have tried the data to text format it does not work

The only way it works is if I copy and paste 1 worksheet as a " Excel 1997 to 2003" worksheet but that means a longer process.

Any suggestions ?

Thank you
  • 0

fave
Rookie
 
Posts: 7
Joined: Mar 30, 2012
Reputation: 0
Excel Version: 2010

Re: EXCEL - VLOOKUP

Postby pecoflyer » Fri Mar 30, 2012 11:12 am

Hi
maybe post a sample of your data showing were things go wrong?
  • 0

A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010

Re: EXCEL - VLOOKUP

Postby Sisyphus » Sun Apr 01, 2012 11:12 pm

Hi,
This seems to be an Excel version problem as opposed to one of VLOOKUP.
I would guess that you are trying to run an Excel sheet that was prepared in an earlier version of Excel on Excel 2010. For some reason VLOOKUP isn't aware of the transition and tries to read the sheet by its own rules. Now the question is if VLOOKUP is using 2010 rules on a sheet that thinks it is 1997 or is it the other way around?

  1. I would try to isolate the two problems. Create a new workbook in Excel 2010. Copy the worksheet in question into this workbook by various ways until you feel sure that it is now of 2010 version.
  2. Delete and re-write the VOOKUP formula in one of the cells where it occurs. The idea would be that Excel 2010 would use a 2010 library to create a new VLOOKUP. That new formula should behave different from any which are confused.

If the above doesn't work I would try to import the sheet into 2010 without the VLOOKUP. This, with the idea in mind that the presence of VLOOKUP in the worksheet might work to prevent a clean transition between versions.
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: EXCEL - VLOOKUP

Postby fave » Mon Apr 02, 2012 8:02 am

Thanks I am going to try this out for sure. You are right I did upgrade to Excel 2010 a few months ago.
  • 0

fave
Rookie
 
Posts: 7
Joined: Mar 30, 2012
Reputation: 0
Excel Version: 2010


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 17 guests