Max function on header row to roll up max date of contiguous range immediately following the header row

[excelpinto]

Hey all,

I'm trying to come up with a max function that will be used on a header row that will roll up the max date of contiguous range immediately following the header row

Here is some sample data

Col A Col B
1 Max Date
1 5/1/2010
1 5/10/2010
1 6/1/2010
2 Max Date
2 5/5/2010
2 5/15/2010
2 6/1/2010


this is in cell b1
=max(B2:??)
where ?? = the last row where col a = 1 so the formula would return max(b2:b4) but I'm having trouble returning b4. I'm not quite sure how to integrate a match or if function into the second half of the max (after b2).

 

[Andrew Poulsom]

One way:

=MAX(IF(A$2:A$8=A2,B$2:B$8))

confirmed with Ctrl+Shift+Enter.

 

[excelpinto]

One way:

=MAX(IF(A$2:A$8=A2,B$2:B$8))

confirmed with Ctrl+Shift+Enter.

Awesome. That works. THANKS! On to another complication:

The keys in column A are text values, in this format:
<table x:str="" style="border-collapse: collapse; width: 129pt;" border="0" cellpadding="0" cellspacing="0" width="172"><tbody><tr style="height: 12.75pt;" height="17"></tr><tr style="height: 20.25pt;" height="27"><td class="xl25" style="height: 20.25pt; width: 48pt;" x:num="" width="64" height="27">1</td><td class="xl26" style="width: 81pt;" x:err="#VALUE!" align="center" width="108">#VALUE!
</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl27" style="height: 12.75pt;" height="17">1.1</td><td class="xl24" x:err="#VALUE!" align="center">#VALUE!</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="height: 12.75pt;" height="17">1.1.1</td><td class="xl23" x:num="40360" align="right">7/1/2010</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="height: 12.75pt;" height="17">1.1.2</td><td class="xl23" x:num="40391" align="right">8/1/2010</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl27" style="height: 12.75pt;" height="17">1.2</td><td class="xl24" x:err="#VALUE!" align="center">#VALUE!</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="height: 12.75pt;" height="17">1.2.1</td><td class="xl23" x:num="40452" align="right">10/1/2010</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="height: 12.75pt;" height="17">1.2.2</td><td class="xl23" x:num="40483" align="right">11/1/2010</td></tr></tbody></table>

The idea is that the '1' header row rolls up all the 1.*.* rows. The '1.1' header row only rolls up the 1.1.* rows. The '1' row is easy and i have that working with =MAX(IF(LEFT(A2:A1000,1)=LEFT(A1,1),B2:B1000)) But I'm struggling with coming up with a working formula for the '1.1' roll up of '1.1.*' I need to use some string functions that will break apart the keys in column a as they can be >2 characters sometimes (like 1.10.10) so I cant just use a straight left,3 function to return it like like I do for the '1' roll up.
This is what I've got so far for the '1.1' rollup:

=MAX(IF(LEFT(A3:A1000,FIND(".",A3:A1000,3)-1)=A2,B3:B1000)) and I'm getting a VALUE error.

Basically i'm looking to return the max of rows below the 1.1 row where their key is 1.1.*. My string function =LEFT(A3,FIND(".",A3,3)-1) appears to work by itself and correctly return only the 1.1 value but when i plug it into the array it returns VALUE Any ideas?

<table x:str="" style="border-collapse: collapse; width: 129pt;" border="0" cellpadding="0" cellspacing="0" width="172"><col style="width: 48pt;" width="64"> <col style="width: 81pt;" width="108"> <tbody><tr style="height: 20.25pt;" height="27"> <td class="xl25" style="height: 20.25pt; width: 48pt;" x:num="" width="64" height="27">
</td> <td class="xl26" style="width: 81pt;" x:err="#VALUE!" align="center" width="108">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl24" x:err="#VALUE!" align="center">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl23" x:num="40360" align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td><td class="xl23" x:num="40391" align="right">
</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl27" style="height: 12.75pt;" height="17">
</td><td class="xl24" x:err="#VALUE!" align="center">
</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="height: 12.75pt;" height="17">
</td><td class="xl23" x:num="40452" align="right">
</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="height: 12.75pt;" height="17">
</td><td class="xl23" x:num="40483" align="right">
</td></tr></tbody></table>

 

[Andrew Poulsom]

How about?

=MAX(IF(LEFT(A$1:A$1000,LEN(A1))=A1,B$1:B$1000))

 

[excelpinto]

How about?

=MAX(IF(LEFT(A$1:A$1000,LEN(A1))=A1,B$1:B$1000))

How about...thats PERFECT. Why do I try to over complicate things?!?!

Thanks Andrew!!