Array Functions in Rows Greater Than 32705

[Dave_Lefebvre]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I was pleased to discover that I could have Spreadsheets with more than 32705 rows in excel 2008.

I have found that excel will not accept an array function if there is a reference that is greater than 32705.

For example

{=IF(OR(B32706=Array_Name),1,"")} placed in Cell C32705

works fine

But if it is placed in Cell C32706

It does not even appear in the cell. After you type it in and hit <Command>-<Enter> it simply disappears

Array_Name is a lookup array
This formula marks the row with a 1 if the value in column B is contained in the lookup array.

Please Help - need to do this in a sheet with 800,000 rows.

Thanks!

 

[JE McGimpsey]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I was pleased to discover that I could have Spreadsheets with more than 32705
rows in excel 2008.

I have found that excel will not accept an array function if there is a
reference that is greater than 32705.

For example

{=IF(OR(B32706=Array_Name),1,"")} placed in Cell C32705

works fine

But if it is placed in Cell C32706

It does not even appear in the cell. After you type it in and hit
<Command>-<Enter> it simply disappears

Array_Name is a lookup array
This formula marks the row with a 1 if the value in column B is contained in
the lookup array.

Hmmm... I cannot reproduce this in XL2008 (version 12.1.1 - have you
updated fully?). I can array enter your formula in C32706 (or C1032706)
without a problem.

How is Array_Name defined?

There's nothing special about row 32705. Excel 2004 has 65,536 rows, and
XL08 has 1,048,576 rows.

FWIW, this equivalent function is more efficient (non-array-entered):

=IF(COUNTIF(Array_Name,B32706),1,"")