CODEWITHSUNDEEP
excelexcel-2013worksheet-function
BellHopByDayAmetuerCoderByNigh
BellHopByDayAmetuerCoderByNigh

Reputation: 1167

Formulas Throw Invalid Reference

I have a worksheet that has just over 1K rows on it, and I am wanting to get a list of what ID's exist in the second worksheet that holds roughly 600 rows. I have tried using the VLOOKUP(), IF(), COUNTIF() functions, but each one gives me an error of

Invalid reference.
This file version can only have formulas that reference cells within a worksheet size of 256 columns (column IW or higher) or 65536 rows.

What do I need to do so that I am able to compare these two lists?

VLookup() I used = =VLOOKUP(A3,[Book1]Sheet1!$A:$K,2,false)
CountIF() I used = =COUNTIF([Book1]Sheet1!$A:$A,D1)

Upvotes: 1

Views: 21004

Answers (3)

Ahmad Bagadood
Ahmad Bagadood

Reputation: 124

Sometimes the file name is too long or contains irregular expression. Try renaming the files to a simple standard alphanumeric name that is relatively short. That fixed my issue.

Upvotes: 0

Saul G
Saul G

Reputation: 11

If you still get this error after converting your XLS file to XLSX, try closing the file completely and then reopening it. It fixed the issue for me.

Upvotes: 1

Alfabravo
Alfabravo

Reputation: 7589

As discussed in comments, the thing with recent versions is about formulas working on a compatibility mode. Having more/less rows available brings up these kind of errors.

Are you working in excel 2013 with a .xls file? If you switch to work in a .xlsx file it might just work.

Upvotes: 1

Related Questions