P
PAL
I have a massive spreadsheet, one of the worksheets has several thousand
rows, many columns.
The sheet I am working on has about 200 rows.
One of my columns is this:
{=IF(ISERROR(SUMPRODUCT(--('Raw Deviation'!A$2:$A$6016=$A2),--('Raw
Deviation'!$D$2:$D$6016="Deviation"),--('Raw
Deviation'!$B$2:$B$6016=H$1))/$E2),"",(SUMPRODUCT(--('Raw
Deviation'!A$2:$A$6016=$A2),--('Raw
Deviation'!$D$2:$D$6016="Deviation"),--('Raw
Deviation'!$B$2:$B$6016=H$1))/$E2))}
Works great when I reference H$1 or a "text value". When I move over a
column:
{=IF(ISERROR(SUMPRODUCT(--('Raw Deviation'!$A$2:B$6016=$A2),--('Raw
Deviation'!$D$2:$D$6016="Deviation"),--('Raw
Deviation'!$B$2:$B$6016=I$1))/$E2),"",(SUMPRODUCT(--('Raw
Deviation'!$A$2:B$6016=$A2),--('Raw
Deviation'!$D$2:$D$6016="Deviation"),--('Raw
Deviation'!$B$2:$B$6016=I$1))/$E2))}
So, the only thing changed is =I$1. If I replace I$1 with "text" it works.
I even tried retyping I1. I would rather reference since the text could
change.
Ideas. Is the spreadsheet simply too big?
rows, many columns.
The sheet I am working on has about 200 rows.
One of my columns is this:
{=IF(ISERROR(SUMPRODUCT(--('Raw Deviation'!A$2:$A$6016=$A2),--('Raw
Deviation'!$D$2:$D$6016="Deviation"),--('Raw
Deviation'!$B$2:$B$6016=H$1))/$E2),"",(SUMPRODUCT(--('Raw
Deviation'!A$2:$A$6016=$A2),--('Raw
Deviation'!$D$2:$D$6016="Deviation"),--('Raw
Deviation'!$B$2:$B$6016=H$1))/$E2))}
Works great when I reference H$1 or a "text value". When I move over a
column:
{=IF(ISERROR(SUMPRODUCT(--('Raw Deviation'!$A$2:B$6016=$A2),--('Raw
Deviation'!$D$2:$D$6016="Deviation"),--('Raw
Deviation'!$B$2:$B$6016=I$1))/$E2),"",(SUMPRODUCT(--('Raw
Deviation'!$A$2:B$6016=$A2),--('Raw
Deviation'!$D$2:$D$6016="Deviation"),--('Raw
Deviation'!$B$2:$B$6016=I$1))/$E2))}
So, the only thing changed is =I$1. If I replace I$1 with "text" it works.
I even tried retyping I1. I would rather reference since the text could
change.
Ideas. Is the spreadsheet simply too big?