I am trying to sort (using SORTBY) a filter array on two columns, one which is value derived from another of the columns in the range being filtered.  I get an error saying that there’s a circular reference — which there is.

To be more specific:  I have 3 columns, a word list, the date the word was added, and a column indicating (by 1 or 0) a “rating” for the word.

One of the sorting columns is the date column.  It’s value is based on an XLOOKUP to a chart of words and the date they were added.  Some words have blank dates (unknown). So the date column is derived from the word list column.

Filtering the list on the rating indicator works, but when I try to sort by the looked-up date column, I get the error.

Thanks to anyone who can say how to fix this problemand sort the filtered results by 2 columns.

​I am trying to sort (using SORTBY) a filter array on two columns, one which is value derived from another of the columns in the range being filtered.  I get an error saying that there’s a circular reference — which there is. To be more specific:  I have 3 columns, a word list, the date the word was added, and a column indicating (by 1 or 0) a “rating” for the word. One of the sorting columns is the date column.  It’s value is based on an XLOOKUP to a chart of words and the date they were added.  Some words have blank dates (unknown). So the date column is derived from the word list column. Filtering the list on the rating indicator works, but when I try to sort by the looked-up date column, I get the error. Thanks to anyone who can say how to fix this problemand sort the filtered results by 2 columns.   Read More

​