Get last cell above with data

M

Maramor

New Member
Joined
Dec 16, 2006
Messages
23
Here is my fun :)


I have a formula that does =SUM(SUMIF(A3,"TEST",B3),C2)

Now what I want to do is replace the "C2" with a more dynamic value and get the value of the the last row above current cell that has data.

My reasoning is becuase each of these rows is ordered by a date field and sometimes the date will change. So if a change is made to the dates then reordered the formulas are all messed up giving me a imporper running total.

I am not sure if this is possible.


<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20 width=64>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=64>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=64>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=64>C</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>0.00 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>0.00 </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>0.00 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>0.00 </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>15.00 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>15.00 </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>110.00 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>125.00 </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>0.00 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68> </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>700.00 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68> </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>0.00 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>125.00 </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>35.00 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>160.00 </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>6.00 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>166.00 </TD></TR></TBODY></TABLE>

So like in this example. C8 uses C5 and sums with B8.
=SUM(SUMIF(A8,"TEST",B8),C5)

Hopefully someone has a idea :).
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Try

=IF(A2="TEST",B2+MAX(C$1:C1),"")

Note, this assumes that row 1 contains headers, and that the header on the running total column is not a number.
 
Upvote 0
Found one challenge. What about negatives? It ends up doing a double negative.
I should have included that in my sample above.

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2745504 class=xl67 height=21 width=64>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl69 width=64>C</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=64>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=64>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=64>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>15</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=64>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>110</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>125</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>700</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=64>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>125</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=64>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>35</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>160</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=21 width=64>TEST</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=64 align=right>6</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl72 width=64 align=right>166

</TD></TR>

<TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=64>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>-410</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>-244</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=64>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>35</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>-209</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=21 width=64>TEST</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=64 align=right>-769</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl72 width=64 align=right>-978</TD></TR></TBODY></TABLE>
 
Upvote 0
I found that instead of max I needed the last value with data.
C in the example below is static (what I want).
D is the formula version.
First row is static not sure how to to avoid but I can make that work.
2nd row starts the formula.

=IF(A16="TEST",B16+INDEX(C$16:C16,MATCH(9.99999999999999E+307,C$16:C16)),"")


<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2745504 class=xl67 height=21 width=64>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl69 width=64>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl69 width=64>D</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=64>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=64>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=64>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>15</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=64>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>110</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>125</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>125</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>700</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=64>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>125</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>125</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=64>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>35</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>160</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>160</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=21 width=64>TEST</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=64 align=right>6</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl72 width=64 align=right>166

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" id=td_post_2745504 class=xl70 width=64 align=right>166</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=64>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>-410</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>-244</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>-244</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=64>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>35</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>-209</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>-209</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=21 width=64>TEST</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=64 align=right>-769</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl72 width=64 align=right>-978</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl70 width=64 align=right>-978</TD></TR></TBODY></TABLE>

Thank you again as your solution showed me the pieces I needed. I should have been more clear in my discription and test case.
 
Upvote 0
I did look at that method originally, but discarded it and went with max for the reason of the first row, didn't even think of the possibility of negative values.

Try

Excel Workbook
ABC
1ABC
2TEST00
3TEST1515
4TEST110125
50
6700
7TEST0125
8TEST35160
9TEST6166
10TEST-410-244
11TEST35-209
12TEST-769-978
Sheet1
 
Upvote 0
Again, awesome! Found a snag when reusing the formula. :)
Another requirement I didn't share :) if you are annoyed just say so LoL. I do apologize.

Row E trigger on "TEST1" and It looks like if not on row 2 it requires a number on row 2.
=IF(A8="TEST1",SUM(B8,IF(ROWS(E$1:E7)>1,LOOKUP(1E+100,E$1:E7),0)),"")
<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=20 width=64>A

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=64>B</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=64>C</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=64>D</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=64>E</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=20 width=64>TEST</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=20 width=64>TEST</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>15</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64>15</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64>15</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=20 width=64>TEST</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>110</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64>125</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64>125</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=20 width=64> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl72 width=64> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=20 width=64> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>700</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl72 width=64> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=20 width=64>TEST</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64>125</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64>125</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=20 width=64>TEST1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>35</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64>160</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=20 width=64>TEST</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>6</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64>166</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64>166</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=20 width=64>TEST</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>-410</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64>-244</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64>-244</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=20 width=64>TEST</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>35</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64>-209</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64>-209</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl69 height=21 width=64>TEST</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70 width=64>-769</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl73 width=64>-978

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" id=td_post_2746570 class=xl71 width=64>-978</TD>

<TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=64> </TD></TR></TBODY></TABLE>
 
Upvote 0
Easily fixed :) try

Sheet8

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">A</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">B</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">TEST</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">TEST1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">TEST</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">0</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">TEST</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">15</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">15</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">TEST</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">110</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">125</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">0</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">700</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">TEST</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">125</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">TEST1</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">35</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">35</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">TEST</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">6</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">131</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">TEST</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">-410</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">-279</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">TEST</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">35</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">-244</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">TEST</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">-769</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">-1013</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C2</TD><TD>=IF($A2=C$1,SUM($B2,IF(COUNT(C$1:C1)>1,LOOKUP(1E+100,C$1:C1),0)),"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
I've assumed that you would have TEST, TEST1, etc as the headings on the columns where those results are shown, in which case this is a "1 fits all" formula, enter the first, then copy right and down as needed.
 
Upvote 0
I am impressed, I thought I would have stumped you at least once through this process. :) Actually my headings don't match but I just adjusted back to "". That was a good possibility and in the early stages would have been true :).

My final report is used to measure disk space as we retire old systems and bring online new ones showing planned and then actually completed. Now with your help, I can track how much at any given date we have requested and or returned. Since these are project based the planned date and actual date can change, hince the need to re-sort. The end goal will hopefully show a return of disk space to our network team but I at least want a near equal number :). Which is appox. 1TB returned at current estimate :).

Again, Thank you. This report is highly important as it justify's my plan which was more theory until now. I have already gotten verbal apporval based on my early findings.
 
Last edited:
Upvote 0
Maramor said:
I thought I would have stumped you at least once through this process.
Click to expand...

You did, I missed the obvious and simple solution lol.

Depending how critical the blank cells are in the results, either

=SUMIF($A$2:$A2,"TEST",$B$2:$B2)

or

=IF($A2="TEST",SUMIF($A$2:$A2,"TEST",$B$2:$B2),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
617
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
920
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,522
Messages
6,179,292
Members
452,902
Latest member
Knuddeluff

Share this page

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