| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lloking for an easier way to count | Excel Worksheet Functions | |||
| Is there an easier way? | Excel Discussion (Misc queries) | |||
| Procedure to make a dropdown list larger so it's easier to read | Excel Discussion (Misc queries) | |||
| Easier Way? | Excel Worksheet Functions | |||
| Trying to enlarge tab size on worksheet for easier reading | Excel Discussion (Misc queries) | |||
Hybrid Mode
