Get first argument for INDEX from calculated column plus one other question

Yanta

Board Regular
Joined
Aug 28, 2011
Messages
59
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
 
Last edited:

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top