Table problem with adjacent formulas

[koltachin]

Hello

I知 using excel 2010 and I知 facing the following problem
I have copied a large set of data (+/- 72.000 records ) column A to column AJ
In the adjacent columns I created formulas that retrieve information from another file.
Then I created a table as basis for pivot tables.
The problem that I知 facing is that the formulas in the adjacent columns are not copied properly
From row 65537 the problem occurs.
Example
row 65536 is still OK
=ALS(ISLEEG($AC65536)=WAAR;"";INDEX('rekeningnr gekoppeld aan Jaarrekening.xls'!Database;VERGELIJKEN($AC65536;'rekeningnr gekoppeld aan Jaarrekening.xls'!reknr;0);3))

row 65537 returns an error
=ALS(ISLEEG($AC65537)=WAAR;"";INDEX('rekeningnr gekoppeld aan Jaarrekening.xls'!Database;VERGELIJKEN(#VERW!;'rekeningnr gekoppeld aan Jaarrekening.xls'!reknr;0);3))
In some columns excel starts renumbering from number 1 so #VERW is replaced by $AC1

So I think the ancient limit of number of rows is part of the problem
Any idea what is going wrong !?
Any help is greatly appreciated

Roger

[Paul]

Hi Roger,

An ".xls" file, like the one referenced in your formula (rekeningnr gekoppeld aan Jaarrekening.xls) cannot contain more than 65536 rows. XLS is the Excel 97-2003 format. Try saving that workbook as an XLSX (or XLSM file if it has macros) and then check your formula after adjusting the file extension.

Just because you're using Excel 2010, that doesn't allow Excel to create the additional 935,000 rows in older file formats.

Hope that helps!

[koltachin]

Paul

many thanks, this really solved my problem

Roger