Wow Biff, looks like it would do the job, that was my final formula, its
working, but yours looks like a great improvement. Unfortunately, I'm
getting a #Value. Thanks for your help Biff, I dont want to bother any more,
I'll leave it as is. Unless you feel challenged LOL

"Biff" wrote:

That doesn't look anything like the formula you posted before! <g

Is the one you just posted the final *FINAL* formula? <bg

=SUMPRODUCT(I21:S21*OFFSET($I$6,MATCH(A12,$H$1:$H$ 8,0)-6,{0,1,2,3,4,5,6,7,8,9,10}))

Biff

"JavyD" wrote in message
...
Not quite, its not moving over a column for each instance between I and S.
This is my final formula.

=(OFFSET(I21,0,0)*OFFSET($I$6,MATCH(A12,$H$1:$H$8, 0)-6,0))+(OFFSET(I21,0,1)*OFFSET($I$6,MATCH(A12,$H$1: $H$8,0)-6,1))+(OFFSET(I21,0,2)*OFFSET($I$6,MATCH(A12,$H$1: $H$8,0)-6,2))+(OFFSET(I21,0,3)*OFFSET($I$6,MATCH(A12,$H$1: $H$8,0)-6,3))+(OFFSET(I21,0,4)*OFFSET($I$6,MATCH(A12,$H$1: $H$8,0)-6,4))+(OFFSET(I21,0,5)*OFFSET($I$6,MATCH(A12,$H$1: $H$8,0)-6,5))+(OFFSET(I21,0,6)*OFFSET($I$6,MATCH(A12,$H$1: $H$8,0)-6,6))+(OFFSET(I21,0,7)*OFFSET($I$6,MATCH(A12,$H$1: $H$8,0)-6,7))+(OFFSET(I21,0,8)*OFFSET($I$6,MATCH(A12,$H$1: $H$8,0)-6,8))+(OFFSET(I21,0,9)*OFFSET($I$6,MATCH(A12,$H$1: $H$8,0)-6,9))+(OFFSET(I21,0,10)*OFFSET($I$6,MATCH(A12,$H$1 :$H$8,0)-6,10))

"Biff" wrote:

Or, normally entered (not an array):

=SUMPRODUCT(I21:L21*OFFSET($I$6,MATCH(A12,$H$1:$H$ 8,0)-6,0))

Biff

"Biff" wrote in message
...
Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):

=SUM(I21:L21*OFFSET($I$6,MATCH(A12,$H$1:$H$8,0)-6,0))

Biff

"JavyD" wrote in message
...
Is there an easier way to do this?

Basicly the Match is looking at a specific field in A12, which needs
to
be
dynamic as I'm pasting this formula down. Once the field matches, it
will
ofset it based on its Cell Row #-6 rows, then I need it to calculate
I21
*the
matching row/cell reference. I21 needs to offset based on column. It
looks
like a mess, but seems to be doing the job. But I'm sure others in
the
office may get lost, was wondering if there was somethign easier to
do.

=(OFFSET(I21,0,0)*OFFSET($I$6,MATCH(A12,$H$1:$H$8, 0)-6,0))+(OFFSET(I21,0,1)*OFFSET($I$6,MATCH(A12,$H$1: $H$8,0)-6,0))+(OFFSET(I21,0,2)*OFFSET($I$6,MATCH(A12,$H$1: $H$8,0)-6,0))+(OFFSET(I21,0,3)*OFFSET($I$6,MATCH(A12,$H$1: $H$8,0)-6,0))