|
Easier Match/Offset?
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)) |
Easier Match/Offset?
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)) |
Easier Match/Offset?
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)) |
Easier Match/Offset?
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)) |
Easier Match/Offset?
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)) |
Easier Match/Offset?
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)) |
Easier Match/Offset?
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)) |
Easier Match/Offset?
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)) |
Easier Match/Offset?
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)) |
Easier Match/Offset?
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)) |
Easier Match/Offset?
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)) |
Easier Match/Offset?
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)) |
Easier Match/Offset?
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)) |
| All times are GMT +1. The time now is 08:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com