Disply filtered text on another worksheet

droltac

New Member
Joined
Aug 27, 2011
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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.
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top