I have a table of data, and I'm trying to draw the top 30 values in the STATS_P[PTS] column and list those values sequentially to GAME_P!F8:F37. There is one criteria in the formula, and that is matching GAME_P!$A$2 to the values in STATS_P[REPLAY] column. This is working great at this point. My problem comes when trying to pull the relative row position of those top 30 values (This shows in GAME_P!C8:C37). The results in GAME_P!C8:C37 are reflecting relative positions WITHOUT the desired criteria of matching GAME_P!$A$2 to the STATS_P[REPLAY] column as I did successfully in GAME_P!F8:F37. I can't figure out a way to enter and use the criteria of matching GAME_P!$A$2 to the STATS_P[REPLAY] column in the formula, to only pull the relative positions of those top 30 values that match the selected Replay.
FORMULA in GAME_P!F8:F37 that is correctly pulling appropriate Top 30 values with matching GAME_P!$A$2 to the values in STATS_P[REPLAY] column:
=IF($A$2="(All)",AGGREGATE(14,6,INDIRECT($B$3),D8),AGGREGATE(14,6,INDIRECT($B$3)/(STATS_P[REPLAY]=VALUE($A$2)),D8))
FORMULA in GAME_P!C8:C37 that is pulling Top 30 values, with no consideration of matching GAME_P!$A$2 to the values in STATS_P[REPLAY] column:
=AGGREGATE(15,6,(INDIRECT($B$3)=$F8)/(INDIRECT($B$3)=$F8)*(ROW(INDIRECT($B$3))-ROW(PLAYERS!$F$1)),$B8)
I've tried many different variations of the formula to accomplish this, but have been unsuccessful. Is there a way to do this?
FORMULA in GAME_P!F8:F37 that is correctly pulling appropriate Top 30 values with matching GAME_P!$A$2 to the values in STATS_P[REPLAY] column:
=IF($A$2="(All)",AGGREGATE(14,6,INDIRECT($B$3),D8),AGGREGATE(14,6,INDIRECT($B$3)/(STATS_P[REPLAY]=VALUE($A$2)),D8))
FORMULA in GAME_P!C8:C37 that is pulling Top 30 values, with no consideration of matching GAME_P!$A$2 to the values in STATS_P[REPLAY] column:
=AGGREGATE(15,6,(INDIRECT($B$3)=$F8)/(INDIRECT($B$3)=$F8)*(ROW(INDIRECT($B$3))-ROW(PLAYERS!$F$1)),$B8)
I've tried many different variations of the formula to accomplish this, but have been unsuccessful. Is there a way to do this?