I am working on a large sized spreadsheet called 'the data' (about 4000
rows, columns to HJ). A2:A4000 contains product codes, B1:HJ1 contains
week ending dates (ie 10/6/01). B2:HJ4000 contain unit sales (per
product code, per week)

The spreadsheet 'summary' I work within contains (sorry I can't just
paste a pic in):
A1: product code
A3: start date
A4: end date

I am trying to find the sum of a given product code from a start date
to an end date. So within 'summary', cell A6 is
=SUMPRODUCT((ISNUMBER(SEARCH(A1,'the data'!A2:A4000)))*INDEX('the
data'!B2:HJ4000,,MATCH(A3,'the data'!B1:HJ1,0)):INDEX('the
data'!B2:HJ4000,,MATCH(A4,'the data'!B1:HJ1,0)))

This formula works great up to a certain point, but at some point it
starts returning a circular reference error. It seems that it arises
from too many cells needing to be searched. I've tried using smaller
data sets, different dates, etc, and i just can't figure out why it
starts to "break".
Thanks in advance for any advice


--
hizzle
------------------------------------------------------------------------
hizzle's Profile: http://www.excelforum.com/member.php...o&userid=29370
View this thread: http://www.excelforum.com/showthread...hreadid=514001