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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

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,114,421
Messages
5,547,824
Members
410,813
Latest member
Vhinzvirgo
Top