I use a formula such as =IF(ROWS($1:1)>COUNT($A$5:$A$1840),"",INDEX(Games!E:E,SMALL($A$5:$A$1840,ROWS($1:1))+4)) to retrieve the value for a given column/row based on the selection from =IF('Analysis Data'!$GU5>0,ROWS($1:1),"")
This compresses a list down to only those that have a non-zero value in the GU column. In this case, called "Technical Fouls".
That's fine when the column is known (E:E in the above example). However, GU contains the game (or column offset), in which the Technical foul was called. For each game I record Court, Referee, referee performance, grade and age group. There may be up to 8 games. Game details start in column I.
So If I called a technical foul in game 5 I'd want to retrieve the values in AC through AG in the Games sheet.
Where I'm stuck in how to modify the first argument of INDEX to pick the correct column in this formula: =IF(ROWS($1:1)>COUNT($A$5:$A$1840),"",INDEX(Games!E:E,SMALL($A$5:$A$1840,ROWS($1:1))+4))
The second question is:
On occasion a referee may call more than one technical foul in a session. If the sum of GD:GK is 0 there were no technical fouls. If it is 1 there was 1 technical foul called in a session. It's unlikely that a referee would call technical fouls in more than two games in a session. BUT It is possible that they call 2 or more in a single game.
I've been asked to produce a report showing all technical fouls called by a referee for all games that person has officiated.
The first part of this post gets the first tech foul called for a session, but I also need to get the tech fouls in the same game if there were more than one, and any tech fouls in any subsequent games.
Please let me know what additional info is needed to clarify.
thanks
This compresses a list down to only those that have a non-zero value in the GU column. In this case, called "Technical Fouls".
That's fine when the column is known (E:E in the above example). However, GU contains the game (or column offset), in which the Technical foul was called. For each game I record Court, Referee, referee performance, grade and age group. There may be up to 8 games. Game details start in column I.
So If I called a technical foul in game 5 I'd want to retrieve the values in AC through AG in the Games sheet.
Where I'm stuck in how to modify the first argument of INDEX to pick the correct column in this formula: =IF(ROWS($1:1)>COUNT($A$5:$A$1840),"",INDEX(Games!E:E,SMALL($A$5:$A$1840,ROWS($1:1))+4))
The second question is:
On occasion a referee may call more than one technical foul in a session. If the sum of GD:GK is 0 there were no technical fouls. If it is 1 there was 1 technical foul called in a session. It's unlikely that a referee would call technical fouls in more than two games in a session. BUT It is possible that they call 2 or more in a single game.
I've been asked to produce a report showing all technical fouls called by a referee for all games that person has officiated.
The first part of this post gets the first tech foul called for a session, but I also need to get the tech fouls in the same game if there were more than one, and any tech fouls in any subsequent games.
Please let me know what additional info is needed to clarify.
thanks
Last edited:
