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))

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))

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))

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))

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))

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))

Unless you feel challenged LOL

I love a challenge!

This would be easy to figure out if I could see what it is I'm trying to
figure out but I can only try to recreate the situation based on the
information I'm getting from your formula. So, try this:

=SUMPRODUCT(I21:S21*OFFSET(I6:S6,MATCH(A12,H1:H8,0 )-6,,,11))

Biff

"JavyD" wrote in message
...
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))

I'm still in shock, I think that worked, let me fool around with it and make
sure its fool proof. Wow. BRB

"Biff" wrote:

Unless you feel challenged LOL

I love a challenge!

This would be easy to figure out if I could see what it is I'm trying to
figure out but I can only try to recreate the situation based on the
information I'm getting from your formula. So, try this:

=SUMPRODUCT(I21:S21*OFFSET(I6:S6,MATCH(A12,H1:H8,0 )-6,,,11))

Biff

"JavyD" wrote in message
...
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))

Wow, it works, it works. all it was was that 11 in Width, I didnt even think
of looking into that option. Amazing. Everyone, listen up, Biff is the
Mannnnn! Thanks Biff. I knew there was a way to unnest that monster of a
forumla I had.

"Biff" wrote:

Unless you feel challenged LOL

I love a challenge!

This would be easy to figure out if I could see what it is I'm trying to
figure out but I can only try to recreate the situation based on the
information I'm getting from your formula. So, try this:

=SUMPRODUCT(I21:S21*OFFSET(I6:S6,MATCH(A12,H1:H8,0 )-6,,,11))

Biff

"JavyD" wrote in message
...
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))

One more thing, just made some references absolute.

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

"Biff" wrote:

Unless you feel challenged LOL

I love a challenge!

This would be easy to figure out if I could see what it is I'm trying to
figure out but I can only try to recreate the situation based on the
information I'm getting from your formula. So, try this:

=SUMPRODUCT(I21:S21*OFFSET(I6:S6,MATCH(A12,H1:H8,0 )-6,,,11))

Biff

"JavyD" wrote in message
...
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))

Added the COLUUMN formula to make it a bit more dynamic in case columns are
added between the I6 and S6. As you can see I already did, its up to T now.
This is an interesting formula. Not bad. Working on any possible kinks.

=SUMPRODUCT(I19:T19*OFFSET($I$6:$T$6,MATCH(A10,$H$ 1:$H$8,0)-6,,,COLUMNS($I$6:$T$6)))

"JavyD" wrote:

One more thing, just made some references absolute.

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

"Biff" wrote:

Unless you feel challenged LOL

I love a challenge!

This would be easy to figure out if I could see what it is I'm trying to
figure out but I can only try to recreate the situation based on the
information I'm getting from your formula. So, try this:

=SUMPRODUCT(I21:S21*OFFSET(I6:S6,MATCH(A12,H1:H8,0 )-6,,,11))

Biff

"JavyD" wrote in message
...
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))

Wow!

Thanks for the "enthusiastic" feedback!

The thing that was throwing me off was MATCH(.......)-6 and trying to figure
out how -6 related to your data setup.

Biff

"JavyD" wrote in message
...
Wow, it works, it works. all it was was that 11 in Width, I didnt even
think
of looking into that option. Amazing. Everyone, listen up, Biff is the
Mannnnn! Thanks Biff. I knew there was a way to unnest that monster of a
forumla I had.

"Biff" wrote:

Unless you feel challenged LOL

I love a challenge!

This would be easy to figure out if I could see what it is I'm trying to
figure out but I can only try to recreate the situation based on the
information I'm getting from your formula. So, try this:

=SUMPRODUCT(I21:S21*OFFSET(I6:S6,MATCH(A12,H1:H8,0 )-6,,,11))

Biff

"JavyD" wrote in message
...
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))

The six is there as a row reference, I could have used Row(), but since its
never going to change, I left it as 6.

"Biff" wrote:

Wow!

Thanks for the "enthusiastic" feedback!

The thing that was throwing me off was MATCH(.......)-6 and trying to figure
out how -6 related to your data setup.

Biff

"JavyD" wrote in message
...
Wow, it works, it works. all it was was that 11 in Width, I didnt even
think
of looking into that option. Amazing. Everyone, listen up, Biff is the
Mannnnn! Thanks Biff. I knew there was a way to unnest that monster of a
forumla I had.

"Biff" wrote:

Unless you feel challenged LOL

I love a challenge!

This would be easy to figure out if I could see what it is I'm trying to
figure out but I can only try to recreate the situation based on the
information I'm getting from your formula. So, try this:

=SUMPRODUCT(I21:S21*OFFSET(I6:S6,MATCH(A12,H1:H8,0 )-6,,,11))

Biff

"JavyD" wrote in message
...
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))