Hi Barry
Yes what you describe is exactly what I am trying to achieve. ie "sort the whole table on the rank values in column K."
MATCH, SMALL & INDEX are entirely new commands to me, but yeah if that is the way to go then it's time I learned how to use them.
Yup I have created separate sheets for each for team. Currently there are only three, but the idea was to create one for any new players that joined us after Lockdown is over as we have a couple of friends that also enjoy the board game.
Below is an example of a team sheet:
The "Played" column is a simple check box
The "Opposition" column is unformulated and you simply type in the team you played, same goes for the "For" and "Against". columns. You then choose from the drop down box the type of result the score created, ie a 7 - 0 is a Landslide .
The points are then determined via a series of IF statements as per below
The totals for the "For, Against & Points" Columns are all just simple SUM formulas of the above cells.
Ranking is a simple SUM of the cells above it and the addition of the total value cell that sits below the team roster.
So yeah for each result in the 1st table that I shared (added it again below) the data is pulled from these individual tables on each teams sheet per the above example. I put them together this way, because for this particular board game your team can gain experience, lose players coz they died on the pitch and new players can be bought. Thus I was trying to kill two birds with one stone, by giving each player a sheet that will hold their players attributes and special skills, as well as allowing them to see how they have historically performed against other teams, whilst also giving me the data to create a League table that sorts the teams on overall ranking (higher being better).
So on the league table below for the column "Played" I am using COUNTIFS to draw the data from the relevant teams sheet. Basically I am just counting if the checkbox is ticked for this one.
For the columns "Landslide, Won, Tie, Lost, Rout" I am using COUNTIF in each cell:
for the columns "For, Against, Points & Ranking", I am using the = SUM for each particular cell in the relevant teams sheet.
My plan then hit a brick a wall due my lack of knowledge.
