Formulae across tabs and knowing which to use

[heron]

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

[NBVC]

Not guaranteeing that I could help you here, but...to best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

[heron]

Okay, thanks for that. Soon as I get a sec, most likely Monday now, I'll Save As my file and trim out lots of extraneous info, then post it here.

Much appreciated.

[heron]

Hiyas. Okay, here's the file (attached) that should, if I've got it even PART of the way right, go some way to helping illustrate my problems. I hope it makes sense but please shout if it doesn't

Thanks again.

[NBVC]

For the 1st question, if you are able to add a column on your Teams sheet that shows what position the player is (matching the actual tab names used), you can indirectly refer to the sheetnames via that new column and then use an Index/Match formula to get the results you want...

The second question, if possible, may require VBA to do what you want.. because overwriting a name will cause the existing formula to reflect the new name only. It does not "remember" what was there before to do your calcs.

[heron]

Hiya NB

Okay, sorry for being dense, can you explain the first point in idiot terms, please Actually, I *think* I get it, but is that something someone could write for me, or is that a major feat?

The second, that's most likely well over my head, but I'll welcome suggestions!

[NBVC]

Q1.

Okay, maybe a simpler way, since you are pulling numbers...

Enter the tabnames in 4 vertical cells (Goalies, Defenders, Midfielders, Strikers) anywhere out of the way in the Teams tab. Then select the 4 cells and go to Insert|Name|Define, and call it "Positions" (without quotes).

Then in I5 enter formula:

Please Login or Register  to view this content.

and copy down and across the table.

Adjust the 100's in the formula to reflect the largest last row number of all sheets.. you can add more rows to be safe (in case you add players anywhere in the future).

You will get 0's where there are no data... until that week's info is filled in.

For Q2, I am not a VBA expert... so it is probably best if you repost only that question in the Programming forum.... (if what I guessed is what you want to do).

Attached is sample of my the formula above applied.... I picked A1:A4 to house the tabnames...

[heron]

You're a legend, sir, you've done so much for me already, thanks a million.

I'll give all that a go and let you know how I get on.