Sorting table containing values returned by array formula

chilisrool

New Member
Joined
Jan 30, 2014
Messages
34
I have a table of data in which multiple data entries are compiled into sum totals based on a number of criteria. The name for each entry in the table is called into column A using an array formula of the form:

=IFERROR(INDEX('Player Seasons'!$E:$E,SMALL(IF(FREQUENCY(IF('Player Seasons'!$D$2:$D$295="REG",MATCH('Player Seasons'!$E$2:$E$295,'Player Seasons'!$E$2:$E$295,0)),ROW('Player Seasons'!$E$2:$E$295)-ROW('Player Seasons'!$E$2)+1),ROW('Player Seasons'!$E$2:$E$295)),ROWS(A$10:A10))),"")

The data corresponding to the name returned by the array formula above is then summed based upon the same criteria using SUMIFS:

=SUMIFS(Table1[GP],'Player Seasons'!$D$2:$D$295,"=REG",'Player Seasons'!$E$2:$E$295,$A10,'Player Seasons'!$A$2:$A$295,">="&$K$1,'Player Seasons'!$A$2:$A$295,"<="&$M$1)

However, using this approach has overridden the sorting functionality of the table, as now, however I try and sort the data the names are always returned in the same order as they are listed in 'Player Seasons'!$E:$E.

Is there any way to recapture the sorting functionality of the table whilst using a similar method to automatically populate the names from 'Player Seasons'!$E:$E or will I have to resort to manually entering and updating the names in my table in order to achieve this?
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

chilisrool

New Member
Joined
Jan 30, 2014
Messages
34
Also I am having a problem where I have conditionally formatted (Bold Italic) some data (the highest value in each column) and I have a macro that hides rows dependent on one value in that row. However I would like the conditional formatting to apply only to the visible cells, so that if the maximum value is in a row that is hidden the conditional formatting is applied to the highest, non-hidden value.

If anyone could provide a solution to either of these problems it would be greatly appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,295
Messages
5,571,388
Members
412,386
Latest member
Yasaman
Top