Formula to apply to multiple worksheets when a condition is met.

Hello again,

Another is this possible question. I have a 10 worksheets for 10 athletes that data is entered into weekly. The attached worksheet is a sample. B5:B12 is the dates of competition which is populated from another table. B3 is the current club record (CR) and this cell is populated from another table containing all club records. C5:C12 is where the athletes times are entered. If the athlete equals or breaks the record on a date in say B13 then the formula in D13 allocates a "CR". Currently what is happening is if another athlete gets a time that is better than D3 (on his/her sheet) but not better than the new CR which is now sheet 1 C13, the formula still alots a CR because the time is better than D3 on this athletes sheet.

Any ideas how to write a formula that will recognise the best CR time in any worksheet and only apply to all other sheets after the specific date that the CR was alotted.


Thanks RunHard