You could copy the formula to another cell, then CopyPastespecial Values to
the other cell.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"George" wrote in message
...
I have the following formula in sheet2, which is a summary from sheet
"MAIN".
This formula is in cell "A14" this is dragged down to cell"A5000". This is
done to columns A to G.

=IF($A14="","",SUMPRODUCT((MAIN!$A$11:$A$5000=$A14 )*(MAIN!$D$11:$D$5000)))

The problem Im having is when I clear contents in sheet "MAIN", it clears
the data in sheet2. Is there a way so that sheet2 keeps a cumulative total
of
sheet "MAIN"?
This will enable me to just have about 100 rows in sheet"MAIN".

--
George