How can I create a formula that can identify a cell after the data has been sorted?

[Bermudez]

Good morning everyone, again I come seeking answers....


In the attached file I have a series of competitors, sorted by class with there class position and associated points on the far right. You will notice in the Class Position column there are 7 competitors that did not finish the race... (DNF) This was calculated by creating a formula that sits in the P column that identifies any value (Time) that was Less than the winner's total time, (C23). This value is not the lowest value but rather the value of the the person who came in first as noted by column B.

My problem is this table gets rearranged after being imported so I can not give the formula in the P column a specific cell address.

Is there some way create a formula that would look for the competitor who is first overall (B23) and then use the cell that is adjacent (C23) to calculate anyone who did not finish. (a Value Less the C23)


As always thank you

For forum.xls

[dosydos]

My idea consists on using a hidden fixed cell. For this example I will use C3.
in C3 use the formula

=VLOOKUP(1,$B$4:$C$55,2,FALSE)

now you can format this cell to have white text so it wont show.

Now change your formulas in column P
=IF(B5="","",IF(B5="DQ",0,IF(C5<$C$3$,"DNF",IF(H5=H4,P4+1,1))))

[TempoAccount]

http://www.excelforum.com/excel-prog...ml#post3575432 sounds like what you're asking. It's mindnumbingly brilliant IMO. It creates a temporary invisible moving shape that moves along with the sort. And it works! No helper columns or hidden cells or properties needed except for the temporary shape. You can see several other approaches earlier in that thread, but all have some compromises, except for the shapes approach.