Formula to extract data from other worksheet

[Guest]

My problem is this. The following formula:

=(('Budget MRS 2007'!$H6*'Budget MRS 2007'!$H7)+('Budget MRS
2007'!$I6*'Budget MRS 2007'!$I7)+('Budget MRS 2007'!$J6*'Budget MRS
2007'!$J7)+('Budget MRS 2007'!$K6*'Budget MRS 2007'!$K7)+('Budget MRS
2007'!$L6*'Budget MRS 2007'!$L7)+('Budget MRS 2007'!$M6*'Budget MRS
2007'!$M7))/I6

Is extracting data from one sheet "Budget MRS 2007" and returning a result.
But I need to use this formula to calculate a lot of data. The above formula
e.g. use cell H6-M6 and H7-M7 but I need results for H11-M11 and H12-M12 and
so forth it increases with 5 cells everytime.

Is there a smart way that I can make a formula or something, so that I don't
have to manually change the cellnumbers that excel needs to extract data
from, but make excel realize that it need to take 5 cell steps every time.

Hope this makes any sence or else dont hesitate to write back for further
explanation.

Thanks

 

[Don Guillett]

Try each of these tests.
=H6*H7+I6*I7
=(H6*H7)+(I6*I7)
=SUMPRODUCT(H6:H7,I6:I7)

 

[Guest]

Another thought ..

Try this in a cell adjacent to your posted expression:
=SUMPRODUCT(OFFSET('Budget MRS 2007'!H$6,ROWS($1:1)*5-5,,,6)*OFFSET('Budget
MRS 2007'!H$7,ROWS($1:1)*5-5,,,6))/I6
It should return the same result as your expression. Copy the formula down
one cell, it'll return a corresponding result, but for H11-M11 and H12-M12
instead (ie an increase of 5 rows down as desired). Copy down as far as
required.

 

[vennesse]

Is there a way for me to extract data from a table when it's updated to
another spreadsheet? I have a table with populated data that needs to be
updated daily, and instead of looking at the whole table. looking for the
latest updates, I would like the latest updates/input to be extracted to
another spreadsheet, which lay out only latest update. My table doesn't
involve any calculation, just comments & remarks +date

venn