Excell 2007 / 2003 Compatability Issue

[ghowell]

I created the following array in Excel 2007 and it works great.

=IF(ISERROR(INDEX('Roll Cleaning Data'!C,SMALL(IF(cleaning_status="
",ROW(cleaning_status)),ROW('Roll Cleaning Data'!1:1)),2))," ",INDEX('Roll
Cleaning Data'!C,SMALL(IF(cleaning_status="
",ROW(cleaning_status)),ROW('Roll Cleaning Data'!1:1)),2))

I then save the file in a 2003 compatable version and when I open the file
the data is correct. However, when I make any changes to the Array in the
Excel 2003 and update the array (ctrl + shift + enter) the cells go blank.

I'm thinking either the formula or some part of it is incompatiable with
2003? Any help appreciated.

Greg

 

[Peo Sjoblom]

You can't use the whole columns in array formulas before 2007, I would
expect it to return a REF error though

--


Regards,


Peo Sjoblom

 

[Shane Devenshire]

Hi,

There is nothing technically wrong with your formula but Excel 2003 and
earlier don't support full column or full row references in array formulas.
So change the C for example to C265536 and it will work.

This is one of the new features in 2007.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Shane Devenshire]

Hi Peo,

Actually it returns NUM or VALUE errors depending on exactly what you are
doing, never seems to return REF

Cheers,
Shane Devenshire