Thread: =IF fomula, transporting data between sheets.

I am in charge of a sports league with about 8 teams in 4 grades, so they each need to be scored separately.

WIN = 4 points
LOSS = 0 points
DRAW = 2 points
Teams can loose 2, 4, or 8 points if they are naughty (like don't referee, or play in the wrong team)

There are about 25 different nights, and before I took over, each grade had it's own spreadsheet and they weren't linked together. You couldn't see who played who on a night which made it a real pain if some of the information was not put in correctly. I would like to put all of the grades and teams into one document, with each game night as a different sheet.

I have lots of questions, but I'll try to put them into a logical order, and deal with 1 at a time then go from there.

If you look at the attachment, Draw.xls, sheet 9March.

This is a draw complete with scores and any applicable penalties.

So, A2 - JG WEGC played JG Chilton. JG WEGC won 5-4. Accordingly, JG WEGC is awarded 4 points, JG Chilton gets 0.

Initial issue:
A3 - JG SHC A has drawn with JG QMC, 5-5. The point allocation should be 2 each, but I can't find a way to make the formula do that. Any ideas?

Next,
I would like to find a way to transport points and scores from B, C, E, F, G and H into the sheet 'Overview'. So, JG WEGC wins, so gets 4 points. That should be added into C8 on the overview sheet as "1" - they have won 1 game. If they win next week, they will have 1 win + 1 win = "2" in the column. JG Chilton loses, so their loss goes into E2.

I don't know how this can be done because each week, some teams have a bye, don't play, so don't feature in the draw. One way could be set all of the teams up like in sheet "Test1". There will be the same number of games each week. The issue I see here is when I sort (filter) by "TEAM", the formulas don't lock to the team, they stay looking at the value in the cell. If I can lock the formula to the team, then when JG WEGC moves, it will still be comparing against the scores of JG Chilton in B14.

If I can do that, then it might be easier to find "JG WEGC" and transport the "Points" into the overview sheet. Penalties also need to be transported, as well as the scores.

The goals scored by JG WEGC (5) need to be added to "Overview">"JG WEGC">"For". The goals scored against JG WEGC (ie, the goals scored by their opposition, JG Chilton) need to be added to "Overview">"JG WEGC">"Against". Each game, the scores will be added together, then "For" divided by "Against" to find "Average"

If anyone can help with these things, I'd really appreciate it. It would make my job so much easier if I can just input scores and the spreadsheet will do the rest.

Hello LMSmith, welcome to the forum.

Please read the forum rules (link in my signature) and then edit your thread title to something descriptive of your issue.

I also recommend breaking such large posts up into smaller, more defined threads with only one or at most two questions on specific issues. Large posts often deter the casual volunteer from even reading them.

Thanks.

I think that would be a good idea, honestly. I'll close this post so you can create new ones. Please remember to give them good thread titles, and really focus on what you want to do (e.g. 'formula to do _____ and copy it to sheet2', etc.)

It's good to have the background info, but as I said, too much info can be daunting.

Thanks!