Automatically Updating Personal Records - Cross Country Question

[Coach Eshelman]

Good morning,

I coach Cross Country and we use a spreadsheet to keep all race times in one location, like a season summary sheet

Column A is name
Column B is personal record
Column C is personal record course/date
Column D is current season best time
Columns E-M are the times my kids run at meets throughout the season.

Column D is =min(e2:m2)....that function gives me their current season best time.

What I am trying to figure out is if column D (season best) is FASTER than the time listed in Column B (personal record)...is there a way for column B to display column D's data automatically?

Thanks in advance!

[JohnTopley]

Try

=IF(D2<B2,D2,"")

Displays if better

[Coach Eshelman]

That works but I have to make a new column...

Column B is Current PR
Column C is New PR
Column E is Season Best

Let's say a girl has a personal best of 19:11 (that is what is typed in Column B)...second race of the year, she goes 18:58. So Season Best column would display 18:58....

How do I setup Column B to have the data entered read 19:11 (her previous PR) switch to 18:58 for her new personal record?

[JohnTopley]

With VBA

Please Login or Register  to view this content.

This code is in Sheet1: click tab>> "View Code"

You will need to enter D2 value to trigger VBA

[JohnTopley]

Another version which works of entries in column E onwards

Please Login or Register  to view this content.

It checks for Season best plus PB

[Coach Eshelman]

I do not know anything about VBA...Can you help explain?

I like the idea of the version working off entries from column e onward (this is ideally what I want)...

I copy and pasted the code, but when I enter a PR time in column e (first race)...the season best column changes (column D), but not the personal best (column b).

Thanks for the help so far...I feel we are close

[JohnTopley]

See post #5 with corrected code!!

It checks for entries in Column E onwards and used MIN formula in Column D to check latest entry is season's best. And checks if a PB has been achieved.

Give it a try.

[Coach Eshelman]

Pretty sure that works!

Thanks so much!!!

[JohnTopley]

If you have an problems come back to the forum.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.