Disply filtered text on another worksheet

[droltac]

I've been stumped on how to do this so I thought I would try here.

This is for an RPG game I play.

This is what I'm trying to do...

I have columns that are text that I filter and I want the filtered text to appear on another worksheet.

The header is 2 rows and the filtered line will be displaying on row 3 (Both on the original worksheet and on the sheet that it will be display on)

I will be filtering it down to just one row which is row 3

Example after being filtered:
<table border="0" cellpadding="0" cellspacing="0" width="884"><colgroup><col style="mso-width-source:userset;mso-width-alt:7716;width:158pt" width="211"> <col style="mso-width-source:userset;mso-width-alt:6473;width:133pt" width="177"> <col style="mso-width-source:userset;mso-width-alt:2560;width:53pt" width="70"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:2596;width:53pt" width="71"> <col style="mso-width-source:userset;mso-width-alt:2084;width:43pt" width="57"> <col style="mso-width-source:userset;mso-width-alt:2157;width:44pt" width="59"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:3401;width:70pt" width="93"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:158pt" height="20" width="211">
</td> <td class="xl65" style="width:133pt" width="177">
</td> <td class="xl66" style="width:53pt" width="70">
</td> <td colspan="2" class="xl68" style="width:104pt" width="139">CF</td> <td colspan="2" class="xl66" style="width:87pt" width="116">Handling</td> <td class="xl66" style="width:59pt" width="78">
</td> <td class="xl66" style="width:70pt" width="93">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">Chassis</td> <td class="xl64">Sub Category</td> <td class="xl66">Body</td> <td class="xl68">Start</td> <td class="xl68">Max</td> <td class="xl66">On</td> <td class="xl66">Off</td> <td class="xl66">Armor</td> <td class="xl66">AutoNav</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">Car</td> <td class="xl65">Sports Car</td> <td class="xl67">3</td> <td class="xl67">3</td> <td class="xl67">18</td> <td class="xl67">4</td> <td class="xl67">8</td> <td class="xl67">0</td> <td class="xl67">0</td> </tr> </tbody></table>
So in this example I would want Car showing up on A3 of Sheet2 and Sports Car on B3.

I have no problems doing this with the columns that contain numbers (By using subtotal) but have not been successful with text.

When I try using =INDEX(Chassis!A:A,(ROW())) it shows the true line 3 instead of the filtered line 3.

 

[T. Valko]

I've been stumped on how to do this so I thought I would try here.

This is for an RPG game I play.

This is what I'm trying to do...

I have columns that are text that I filter and I want the filtered text to appear on another worksheet.

The header is 2 rows and the filtered line will be displaying on row 3 (Both on the original worksheet and on the sheet that it will be display on)

I will be filtering it down to just one row which is row 3

Example after being filtered:
<TABLE cellSpacing=0 cellPadding=0 width=884 border=0><COLGROUP><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7716" width=211><COL style="WIDTH: 133pt; mso-width-source: userset; mso-width-alt: 6473" width=177><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" width=59><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93></COLGROUP><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="WIDTH: 158pt; HEIGHT: 15pt" width=211 height=20>

</TD><TD class=xl65 style="WIDTH: 133pt" width=177>

</TD><TD class=xl66 style="WIDTH: 53pt" width=70>

</TD><TD class=xl68 style="WIDTH: 104pt" width=139 colSpan=2>CF</TD><TD class=xl66 style="WIDTH: 87pt" width=116 colSpan=2>Handling</TD><TD class=xl66 style="WIDTH: 59pt" width=78>

</TD><TD class=xl66 style="WIDTH: 70pt" width=93>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="HEIGHT: 15pt" height=20>Chassis</TD><TD class=xl64>Sub Category</TD><TD class=xl66>Body</TD><TD class=xl68>Start</TD><TD class=xl68>Max</TD><TD class=xl66>On</TD><TD class=xl66>Off</TD><TD class=xl66>Armor</TD><TD class=xl66>AutoNav</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" height=20>Car</TD><TD class=xl65>Sports Car</TD><TD class=xl67>3</TD><TD class=xl67>3</TD><TD class=xl67>18</TD><TD class=xl67>4</TD><TD class=xl67>8</TD><TD class=xl67>0</TD><TD class=xl67>0</TD></TR></TBODY></TABLE>
So in this example I would want Car showing up on A3 of Sheet2 and Sports Car on B3.

I have no problems doing this with the columns that contain numbers (By using subtotal) but have not been successful with text.

When I try using =INDEX(Chassis!A:A,(ROW())) it shows the true line 3 instead of the filtered line 3.

Let's assume you have a table of data in the range A2:D15. A1:D1 are column headers and you have an autofilter applied to this data.

This array formula** will return the first visible cell from the filtered range A2:A15:

=INDEX(A2:A15,MATCH(1,(SUBTOTAL(3,OFFSET(A2:A15,ROW(A2:A15)-MIN(ROW(A2:A15)),0,1)))*(A2:A15<>""),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Let's assume you enter that formula in A20. Then, just copy across to D20 to get the values from the rest of the filtered row.

 

[droltac]

That did it, thanks very much.

 

[T. Valko]

That did it, thanks very much.

You're welcome. Thanks for the feedback!