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

=IF(ISERROR(INDEX('Roll Cleaning Data'!C:D,SMALL(IF(cleaning_status="
",ROW(cleaning_status)),ROW('Roll Cleaning Data'!1:1)),2))," ",INDEX('Roll
Cleaning Data'!C:D,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

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

"ghowell" wrote in message
...
I created the following array in Excel 2007 and it works great.

=IF(ISERROR(INDEX('Roll Cleaning Data'!C:D,SMALL(IF(cleaning_status="
",ROW(cleaning_status)),ROW('Roll Cleaning Data'!1:1)),2))," ",INDEX('Roll
Cleaning Data'!C:D,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

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:D for example to C2:D65536 and it will work.

This is one of the new features in 2007.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"ghowell" wrote:

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

=IF(ISERROR(INDEX('Roll Cleaning Data'!C:D,SMALL(IF(cleaning_status="
",ROW(cleaning_status)),ROW('Roll Cleaning Data'!1:1)),2))," ",INDEX('Roll
Cleaning Data'!C:D,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

Hi Peo,

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

Cheers,
Shane Devenshire

"Peo Sjoblom" wrote:

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

"ghowell" wrote in message
...
I created the following array in Excel 2007 and it works great.

=IF(ISERROR(INDEX('Roll Cleaning Data'!C:D,SMALL(IF(cleaning_status="
",ROW(cleaning_status)),ROW('Roll Cleaning Data'!1:1)),2))," ",INDEX('Roll
Cleaning Data'!C:D,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