HOW TO: Display specific data dependant on a condition by using formulas not filters?

[abupu]

Hi Guys,

This is the problem im having:

<table border="0" cellpadding="0" cellspacing="0" width="281"><colgroup><col style="mso-width-source:userset;mso-width-alt:4169;width:86pt" width="114"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3766;width:77pt" width="103"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt;width:86pt" height="17" width="114">Status</td> <td class="xl1131" style="width:48pt" width="64">Task</td> <td class="xl1131" style="width:77pt" width="103">People</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Discontinued</td> <td class="xl1131">a</td> <td class="xl1130" align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Discontinued</td> <td class="xl1131">b</td> <td class="xl1130" align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Active</td> <td class="xl1131">c</td> <td class="xl1130" align="right">3</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Active</td> <td class="xl1131">d</td> <td class="xl1130" align="right">4</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Discontinued</td> <td class="xl1131">e</td> <td class="xl1130" align="right">5</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Discontinued</td> <td class="xl1131">f</td> <td class="xl1130" align="right">6</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Discontinued</td> <td class="xl1131">g</td> <td class="xl1130" align="right">7</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Discontinued</td> <td class="xl1131">h</td> <td class="xl1130" align="right">8</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Discontinued</td> <td class="xl1131">i</td> <td class="xl1130" align="right">9</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1130" style="height:12.75pt" height="17">Active</td> <td class="xl1131">j</td> <td class="xl1130" align="right">10</td> </tr> </tbody></table>
I have this data table on Sheet 1 and i want to display ONLY the rows that show "Active" on another sheet. Also, i want to be able to display only the columns I would like on another sheet.

An example would be I want to display all rows with the Status as "Active" and only the People column on Sheet 2. This output should look like this:

<table border="0" cellpadding="0" cellspacing="0" width="103"><colgroup><col style="mso-width-source:userset;mso-width-alt:3766;width:77pt" width="103"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl1132" style="height:12.75pt;width:77pt" height="17" width="103">People</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt" align="right" height="17">3</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt" align="right" height="17">4</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1131" style="height:12.75pt" align="right" height="17">10</td> </tr> </tbody></table>
I require to do this without the use of filters and the data should update (increase or decrease in rows) if the Status of other rows are changed to "Active" on Sheet 1.

Any ideas?

 

[Aladin Akyurek]

It worked! Thanks so much Aladin!!!!

You are welcome. Thanks for providing feedback.

 

[T. Valko]

Hi Guys,

This is the problem im having:

<TABLE cellSpacing=0 cellPadding=0 width=281 border=0><COLGROUP><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103></COLGROUP><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1131 style="WIDTH: 86pt; HEIGHT: 12.75pt" width=114 height=17>Status</TD><TD class=xl1131 style="WIDTH: 48pt" width=64>Task</TD><TD class=xl1131 style="WIDTH: 77pt" width=103>People</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1130 style="HEIGHT: 12.75pt" height=17>Discontinued</TD><TD class=xl1131>a</TD><TD class=xl1130 align=right>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1130 style="HEIGHT: 12.75pt" height=17>Discontinued</TD><TD class=xl1131>b</TD><TD class=xl1130 align=right>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1130 style="HEIGHT: 12.75pt" height=17>Active</TD><TD class=xl1131>c</TD><TD class=xl1130 align=right>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1130 style="HEIGHT: 12.75pt" height=17>Active</TD><TD class=xl1131>d</TD><TD class=xl1130 align=right>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1130 style="HEIGHT: 12.75pt" height=17>Discontinued</TD><TD class=xl1131>e</TD><TD class=xl1130 align=right>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1130 style="HEIGHT: 12.75pt" height=17>Discontinued</TD><TD class=xl1131>f</TD><TD class=xl1130 align=right>6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1130 style="HEIGHT: 12.75pt" height=17>Discontinued</TD><TD class=xl1131>g</TD><TD class=xl1130 align=right>7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1130 style="HEIGHT: 12.75pt" height=17>Discontinued</TD><TD class=xl1131>h</TD><TD class=xl1130 align=right>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1130 style="HEIGHT: 12.75pt" height=17>Discontinued</TD><TD class=xl1131>i</TD><TD class=xl1130 align=right>9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1130 style="HEIGHT: 12.75pt" height=17>Active</TD><TD class=xl1131>j</TD><TD class=xl1130 align=right>10</TD></TR></TBODY></TABLE>
I have this data table on Sheet 1 and i want to display ONLY the rows that show "Active" on another sheet. Also, i want to be able to display only the columns I would like on another sheet.

An example would be I want to display all rows with the Status as "Active" and only the People column on Sheet 2. This output should look like this:

<TABLE cellSpacing=0 cellPadding=0 width=103 border=0><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103></COLGROUP><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1132 style="WIDTH: 77pt; HEIGHT: 12.75pt" width=103 height=17>People</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1131 style="HEIGHT: 12.75pt" align=right height=17>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1131 style="HEIGHT: 12.75pt" align=right height=17>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1131 style="HEIGHT: 12.75pt" align=right height=17>10</TD></TR></TBODY></TABLE>
I require to do this without the use of filters and the data should update (increase or decrease in rows) if the Status of other rows are changed to "Active" on Sheet 1.

Any ideas?

See this...

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3