Big SUMPRODUCT problem

[CLR]

Hi All........
In XL2k, I have a 3500 line database and the rightmost 2 columns I insert
two large SUMPRODUCT formulas. One is wrapped with an ISERR statement and
the other is part of a LOOKUP formula. Both were written ok and were WERE
WORKING FINE FOR A COUPLE OF WEEKS. All of a sudden they don't work.

I've taken that same sheet out of an old version of the workbook where the
SUMPRODUCT formulas are still working and put it in this workbook and it
works fine as long as the SUMPRODUCT formulas are still referring to the
older workbooks database, but when I copy them over to the current database
sheet they fail just as the original ones do. This is pretty important to
me as I use the results of these formulas as a basis for many "Reports".
Any ideas?.......

TIA
Vaya con Dios,
Chuck, CABGx3

 

[Gary''s Student]

I suspect that the problem is in the data and not in the formulas.

You may need to back-off your recent changes until you find the offending
data items.

 

[CLR]

Thanks, I'll give that a try.....however, I forgot to mention that I have
other SUMPRODUCT formulas on another sheet that refer to this same database
that still work........but I will try switching databases with an older one
that still worked.......it is an annual database with about 250-300 ines per
month, and once a month I remove the oldest month and add in the current
month, so will check out that process too........

Many thanks,
Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Well, your suggestion seemed to hit at the heart of the problem. I replaced
the database with an older one where the SUMPRODUCT formulas still worked
and the problem went away! Then, I was able to recreate the problem by
running a routine I have whereby I keep a GOLD copy of all the formulas in
the sheet in ROW 1, and keep the database at Copy > PasteSpecial > Values,
so I don't have so many formulas to save and recalculate.........well it
happens when I copy down one of the columns that is used in the SUMPRODUCT
formulas, that is when all the SUMPRODUCT formulas go bad.......so, maybe I
need to store the GOLD formulas in a macro rather than on ROW 1, because
even the ROW 1 versions of the SUMPRODUCT formulas go bad with the
rest.......then it's all downhill from there........

Anyway.......you showed me the light and was of a GREAT HELP........many
thank-you's,

Vaya con Dios,
Chuck, CABGx3