Help with a Vlookup/max type situation

OK. The following spreadsheet I have attached is a drainage design spreadsheet. On Tab 3-Pipe Design, there is column R - time of concentration.
Cell R32 is representative of how the formula for time of concentration must work, ie. the maximum time of concentration must be used. Cell R37 shows how I would like it to work (or atleast my attempts) so that excel finds the maximum TofC automatically.

The reason for this: redesigning a storm system can be very cumbersome. We had the spreadsheet, but adding a manhole in the middle of a pipe run resulted in a lot of re-work. I set out to find a way to make adding manholes / revisions more automated with excel by trying to break down components and re-combine them.

Each trunkline may have lateral sewer leads that feed it (think of a road side drainage system). The time of concentration is basically how much time it takes the water to get from manhole A to manhole B. Now if I have 2 lines that connect to manhole B (think of a Y-connection), where Manhole A connects to B and Manhole C connects to B. I want the excel function to find which run (a-b or c-b) has the max TofC and use that value.

Please see my spreadsheet from rapidshare (the attach function below would not work)

http://rapidshare.com/files/19700626...plate.zip.html


P.S. I did try indexing - see far far far right side of worksheet. But, I was unsuccessfull.


I am open to any suggestions.

thank You.

Chris

yes that is correct.

What I am trying to accomplish is this:

On some laterals for instance Number 2 (D42 & D197 & D215) it will need to evaluate these three to return a max value (from corresponding R42,R197 & R215), BUT in other instances (EX2) it should just add the R32 to AH32 (and enter this number into R33) (because there are no other EX2 laterals on the spreadsheet).

Is that more clear?

Yes to your reply above..that is what I want in general.

---------------------------
I found this link last night.

This kind of is what I am looking for...in a round-about way.

http://www.mrexcel.com/archive/Formulas/24205.html

I want to look up manholes (ie EX 2 or number 2 or whatever is in Column D) and compare values in the R column for the looked up manhole in column D. I then want to compare the values of col R and output the max value to the cell below (in column R).

I'm hoping in the end that I don't creat circular references. I guess that's the big part to figure out.

by the way...the above link I provided...worked perfect for what I needed.

It helped me to compartmentalize the individual numbers, compare them, use max values, then recombine them. worked quite nicely!!