Auto-populate cells from table, but not the ones with zero balances

[bdpatton2]

Table 1 Looks like mine table. (but mines actually 34 rows long including the header)​

TABLE1
<TABLE style="WIDTH: 343pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=457><COLGROUP><COL style="WIDTH: 118pt; mso-width-source: userset; mso-width-alt: 6698" width=157><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 4266" width=100><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 4266" span=2 width=100><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 118pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=16 width=157>Sources </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=100>Brand 1 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=100>Brand 2 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=100>Brand 3 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=15>Animation </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>5 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>7</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>8</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=15>Apparel </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>0</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>0</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=15>Audio Mentions </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>7</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>8</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=15>Back Panel </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>7</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>8</TD></TR></TBODY></TABLE>​

Essentially, I would like to be able to make a supplemental graph next to the graph above that autopopulated when the numbers change, but did not include sources like "Apparel" that has a total value of zero.


So I tried making a new table next to the current one I had that would pull sources and their corresponding values, while leaving the zero value sources out of the new table. Like the one below (notice: no Apparel Source because it has no value). Table 2 would autopopulate from the original table.


TABLE2
<TABLE style="WIDTH: 343pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=457><COLGROUP><COL style="WIDTH: 118pt; mso-width-source: userset; mso-width-alt: 6698" width=157><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 4266" width=100><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 4266" span=2 width=100><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 118pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=16 width=157>Sources </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=100>Brand 1 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=100>Brand 2 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=100>Brand 3 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=15>Animation </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>5 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>7</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>8</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=15>Audio Mentions</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>7</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>8</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=15>Back Panel</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>7</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>8</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=15></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68></TD></TR></TBODY></TABLE>
I'm hoping that someone can provide me with the formula i'm looking for or a better procedure if they know of another more-efficient way to go about this.

So in summary,
1) I have a table with lots of numbers.

2) Next to that one I would like another table (for graph-purposes)
that only pulled sources that had value (meaning sources greater than 0)

3) This way I can make a stacked bar graph without having it show a "color"
for sources with a zero balance. It'll make the graph cleaner looking.

4) I need someone's help figuring this out.

My Problem: When I dragged (to copy formula) the cell down the formula only showed first row's source and numbers of the original table because the formula was set-up in such a way that it didn't exclude the values above the current row it was one. Here's what my formula looked like.

=IF($G$3>0,$A$3,IF($G$4>0,$A$4,IF($G$5>0,$A$5,IF($G$6>0,$A$6,IF($G$7>0,$A$7,IF($G$8>0,$A$8,IF($G$9>0,$A$9,IF($G$10>0,$A$10,IF($G$11>0,$A$11,IF($G$12>0,$A$12,IF($G$13>0,$A$13,IF($G$14>0,$A$14,IF($G$15>0,$A$15,IF($G$16>0,$A$16,IF($G$17>0,$A$17,IF($G$18>0,$A$18,IF($G$19>0,$A$19,IF($G$20>0,$A$20,IF($G$21>0,$A$21,IF($G$22>0,$A$22,IF($G$23>0,$A$23,IF($G$24>0,$A$24,IF($G$25>0,$A$25,IF($G$26>0,$A$26,IF($G$27>0,$A$27,IF($G$28>0,$A$28,IF($G$29>0,$A$29,IF($G$30>0,$A$30,IF($G$31>0,$A$31,IF($G$32>0,$A$32,IF($G$33>0,$A$33,IF($G$34>0,$A$34,IF($G$35>0,$A$35,"")))))))))))))))))))))))))))))))))



Thanks in advance to the brilliant person who helps me with this.


Ben

I have Excel 2007.

 

[c_m_s_jr]

Try taking a look at using advanced filter. You will just need to refresh the filter results in order to update your graph.