Offset row data

[blackorchids2002]

Hi Everyone,

Is it possible to use offset formula to move to the next row data? I am trying to achieve all H&S data will be captured in sheet 2. I know there is an easy way to do that by filtering it. But I am trying to automate the summary template in sheet by putting a formula from the data in sheet 1 below.

[TABLE="width: 196"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Customer Unit Price[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]H&S[/TD]
[TD="align: right"]68[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]H&S[/TD]
[TD="align: right"]69[/TD]
[/TR]
[TR]
[TD]H&S[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD]H&S[/TD]
[TD="align: right"]71[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


Thanks.

 

[Robert Mika]

Hi Everyone,

Is it possible to use offset formula to move to the next row data? I am trying to achieve all H&S data will be captured in sheet 2. I know there is an easy way to do that by filtering it. But I am trying to automate the summary template in sheet by putting a formula from the data in sheet 1 below.

[TABLE="width: 196"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Customer Unit Price[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]H&S[/TD]
[TD="align: right"]68[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]H&S[/TD]
[TD="align: right"]69[/TD]
[/TR]
[TR]
[TD]H&S[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD]H&S[/TD]
[TD="align: right"]71[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Err[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


Thanks.

Excel 2010

	A	B
Code	Customer Unit Price
Err
Err
Err
Err
Err
H&S
Err
Err
Err
Err
Err
Err
Err
Err
Err
Err
H&S
H&S
H&S
Err
Err

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]68[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]69[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]70[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]71[/TD]

[TD="align: center"]21[/TD]

[TD="align: center"]0[/TD]

[TD="align: center"]22[/TD]

[TD="align: center"]0[/TD]

</tbody>

Sheet1​

Excel 2010

	A	B	C
Excel 07/10	Excel 03
H&S

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]68[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]69[/TD]
[TD="align: right"]69[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]70[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]71[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]=COUNTIF(Sheet1!$A$2:$A$200,"="&A3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$200,SMALL(IF(Sheet1!$A$2:$A$200=$A$3,ROW($A$2:$A$200)-ROW($A$2)+1),ROWS($A$2:A2))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=IF(ROWS($A$3:A3)>$A$2,"",INDEX(Sheet1!$B$2:$B$200,SMALL(IF(Sheet1!$A$2:$A$200=$A$3,ROW($A$2:$A$200)-ROW($A$2)+1),ROWS($A$2:A2))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[blackorchids2002]

Thank you so much Robert!!! This is really perfect!!!

 

[Robert Mika]

Thank you so much Robert!!! This is really perfect!!!

You are welcome.
Thanks for feedback.