XL2010 Problem using Index to refer to a row > 65536

[Gavona]

Is this a limitation of the index function, a problem with a compatibility setting or a workbook corruption?

This formula in cell 65536 works
=INDEX(CC_Hierarchy_C_ALL.xls!CC_Look,$A65536,C$1)

However if I copy it to the next row it becomes
=INDEX(CC_Hierarchy_C_ALL.xls!CC_Look,#REF!,C$1)

This formula works: =A65537

I have saved my workbook in XLSM and XLSB formats, closed them and re-opened the saved copy.

Gavin

 

[strongm]

Sadly I can't replicate this behaviour. All works as expected on my copy of Excel 2010

 

[Gavona]

Interesting. I was able to workaround the issue by using vlookup rather than index and match. Not so quick and efficient.


Gavin

 

[SkipVought]

It's an .xls!

You're referencing a 97-2003 version of Excel, having only 65536 rows!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[strongm]

>You're referencing a 97-2003 version of Excel, having only 65536 rows!

That was my initial thought - but then

>This formula works: =A65537

shouldn't work ...

(nor should you be able to copy from a cell in row 65536 to row 65537 as described in OP, since row 65537 does not exist)