A program I wrote calculates ratings for the winner and loser of a sporting event. As new results are added the ratings are updated, ie from Pre to Post. It works fine. I also need a version in Excel but I have a problem.

Example of the problem: John’s Post Rating of his first match needs to be entered as his Pre Rating in his next match. Easy enough to do by hand, but can Excel AUTOMATICALLY find it and insert it into the correct cell once the name 'John' is entered?

Name Pre Post Result Name Pre Post
John 2000 2015 won Dave 1900 1875
Gary 2250 2220 lost Barry 2050 2080
Paul 1900 1923 won Adam 1960 1937
John 2015 ------ won Allan 1807 -----

This can be done either with in-the-cell Excel formulas (Index() and Match()), or with VBA; it depends on how your existing code is populating the other cells. Also, you can use the VBA 'worksheetfunction.' to gain programmatic access to many of the in-cell functions so you don't have to reinvent functionality.

This post is 19 days old. Have you found the solution yourself yet?