Continuation of formula doesn't work

Dear all,

I am working on a formula that sums several values from 2 different excel sheets into one value, hence it looks somehow like this:
=SUM($B$1*Sheet1!A1+...+$B$20*Sheet1!A20) (simplified)

I don't have any problem with the formula itself, but with automatically continuing it. The formula (typed into C1, Sheet2) contains data from 20 stacked rows (say, A1 to A20). I want to copy it into the field below, this time containing data from the next 20 rows (say, A21 to A40). In the field below, it should be data from A41 to A60 and so on.
However, it seems that I can't just auto-continue my formula - when I do that, excel will use the data from A2-A21 and A3-23, respectively. I also can't solve the problem manually, since I have to copy the formula around 500 times (each time containing data from 20 different rows).

I hope that it is understandable what my problem is - didn't find an easier way to put it
Does anyone have an idea how I can modify my formula, so that I can auto-continue it?

Any help will be highly apreciated.
Best,
Mitja

EDITED BY RON CODERRE (MODERATOR)
This question has also been posted on another forum at this link:
http://www.mrexcel.com/forum/excel-q...esnt-work.html

With
B1:B20 containing numeric factors
and...on Sheet1
Col_A contains values to be multiplied by those factors

This regular formula multiplies B1:B20 times Sheet1!A1:A20 and sums the results:

Please Login or Register  to view this content.

When copied into C2, that formula multiplies B1:B20 times Sheet1!A21:A40 and sums the results.
Each time you copy the formula down a row, the Sheet1 referenced range increments by 20 rows.

Is that something you can work with?