Hi guys, hope you’re able to help.

I’ve got two questions and both are quite complicated to explain. I apologise if I’ve gone the wrong way about explaining them, and will gladly do whatever I can to help resolve it If anyone wants to see the spreadsheet, for example, I guess I could mail it over.

I’ve got a spreadsheet of football stats and the like which I use to run a fantasy football league.

On the first tab, The Teams, there are areas for everyone’s team.

On the rest of the tabs, Goalies, Defenders, Midfielders and Strikers, there are lists for the players. Each has many columns, into which I post the latest score (according to a website where I check them every week).

So my first question is, how do I get each table on The Teams tab to look at the next cell along, on the relevant tab, for a certain player each time I update the scores?

For example, in my team, I have a defender called X, and I want the spreadsheet to know that when I paste in the results to the Defenders tab, it automatically updates Xs score correctly each week, knowing it should look to the next cell along each time.

I assume that’s possible and relatively straightforward?

The second question, and this one is really complicated (as if the other wasn’t!) is how do I get it to reflect transfers? Let me explain. When someone transfers a player, they keep the original player’s score, but it then gets reflected by changes to the NEW player’s score.

For example, let’s say Defender X (above) is on 50 points by the time I want to transfer him. I type over his name with Defender Y. When I paste in the results (I accept I will have to edit the formula to know which player is now in that slot), I would like the spreadsheet to look at the existing score, in this case 50, look at the new score for Defender Y (which might be, say, 120), look at Defender Y’s score last week (which may be, for example, 115), and add that difference to Defender Y’s score in his place on my team.

That’s really complicated and I wish I could think of a better way of explaining it

All help gratefully received, please.

H