Sumproduct

[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?

 

[Max]

From the sounds of it ..

Try replacing this term in your expression:
--('Raw Deviation'!$B$2:$B$6016=I$1)

with this:
--(TRIM('Raw Deviation'!$B$2:$B$6016)=TRIM(I$1))


P/s: Don't think your formula needs to be array-entered
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik

 

[PAL]

Still no good and have checked for extra spaces before and after the text in
I$1.

 

[Max]

Well, thought that was a good shot. Maybe other responders would have other
ideas for you.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik