# Sorting table containing values returned by array formula

#### chilisrool

##### New Member
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?

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### chilisrool

##### New Member
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.

Replies
6
Views
192
Replies
23
Views
558
Replies
0
Views
129
Replies
9
Views
315
Replies
5
Views
180