| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Doesn't work in Excel 2003
I created the following array in Excel 2007 and it works fine.
I then save the spreadsheet to be compatable with 2003 and when I open the file the data is there, but any changes to the data or the array and the cell goes blank. Is it possible that somethign in the formula is incompatable with 2003? Thanks in advance for any help. =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)) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array Doesn't work in Excel 2003
What does "cleaning_status" refer to? In Excel versions prior to Excel 2007
you can't use entire columns as range references. If "cleaning_status" refers to an entire column change it to a smaller specific range. -- Biff Microsoft Excel MVP "ghowell" wrote in message ... I created the following array in Excel 2007 and it works fine. I then save the spreadsheet to be compatable with 2003 and when I open the file the data is there, but any changes to the data or the array and the cell goes blank. Is it possible that somethign in the formula is incompatable with 2003? Thanks in advance for any help. =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)) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array Doesn't work in Excel 2003
Clarification:
In Excel versions prior to Excel 2007 you can't use entire columns as range references. You can't use entire columns as range references *in array formulas*. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... What does "cleaning_status" refer to? In Excel versions prior to Excel 2007 you can't use entire columns as range references. If "cleaning_status" refers to an entire column change it to a smaller specific range. -- Biff Microsoft Excel MVP "ghowell" wrote in message ... I created the following array in Excel 2007 and it works fine. I then save the spreadsheet to be compatable with 2003 and when I open the file the data is there, but any changes to the data or the array and the cell goes blank. Is it possible that somethign in the formula is incompatable with 2003? Thanks in advance for any help. =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)) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array Doesn't work in Excel 2003
That formula looks compatible, but the value if true on the iserror is a
blank. Are you sure the changes to the data aren't producing this result? Is anything in the data calculated by formulas, and if so what are the formulas? "ghowell" wrote: I created the following array in Excel 2007 and it works fine. I then save the spreadsheet to be compatable with 2003 and when I open the file the data is there, but any changes to the data or the array and the cell goes blank. Is it possible that somethign in the formula is incompatable with 2003? Thanks in advance for any help. =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)) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array Doesn't work in Excel 2003
T. Valko's nailed it...the range "Cleaning Status" was in fact a full column.
Shortened it to 10,000 rows, boom. Problem fixed. Thanks! g "~L" wrote: That formula looks compatible, but the value if true on the iserror is a blank. Are you sure the changes to the data aren't producing this result? Is anything in the data calculated by formulas, and if so what are the formulas? "ghowell" wrote: I created the following array in Excel 2007 and it works fine. I then save the spreadsheet to be compatable with 2003 and when I open the file the data is there, but any changes to the data or the array and the cell goes blank. Is it possible that somethign in the formula is incompatable with 2003? Thanks in advance for any help. =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)) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array Doesn't work in Excel 2003
Yeah, he's sharp like that.
"ghowell" wrote: T. Valko's nailed it...the range "Cleaning Status" was in fact a full column. Shortened it to 10,000 rows, boom. Problem fixed. Thanks! g "~L" wrote: That formula looks compatible, but the value if true on the iserror is a blank. Are you sure the changes to the data aren't producing this result? Is anything in the data calculated by formulas, and if so what are the formulas? "ghowell" wrote: I created the following array in Excel 2007 and it works fine. I then save the spreadsheet to be compatable with 2003 and when I open the file the data is there, but any changes to the data or the array and the cell goes blank. Is it possible that somethign in the formula is incompatable with 2003? Thanks in advance for any help. =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)) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array Doesn't work in Excel 2003
by design, a blank is what i wanted returned "on error".
thanks to both of your for your help. "~L" wrote: That formula looks compatible, but the value if true on the iserror is a blank. Are you sure the changes to the data aren't producing this result? Is anything in the data calculated by formulas, and if so what are the formulas? "ghowell" wrote: I created the following array in Excel 2007 and it works fine. I then save the spreadsheet to be compatable with 2003 and when I open the file the data is there, but any changes to the data or the array and the cell goes blank. Is it possible that somethign in the formula is incompatable with 2003? Thanks in advance for any help. =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)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using a two-dimensional array in Excel 2002 or 2003 | New Users to Excel | |||
| Formulas assignment from array to range in VSTO Excel doesn't work | Excel Worksheet Functions | |||
| Is it poss to have a double array in Excel 2003? | Excel Worksheet Functions | |||
| Table array in VLOOKUP (EXcel 2003) | Excel Worksheet Functions | |||
| Find does not work in Excel 2003 | Excel Discussion (Misc queries) | |||
Linear Mode
