Sortby and Filter together - Circular Reference Error

Question

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.

hansvogelaar · Answer

Soproni99&nbsp;
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

soproni99 · Answer

HansVogelaar&nbsp;thank you very kindly for being willing to look at the problem, which may help others. I am working on making a reduced and simplified version of the workbook that will show the full problem, and will soon be able to submit it. &nbsp;

soproni99 · Answer

HansVogelaar&nbsp;I managed to overcome the problem with some further effort. &nbsp;I’m sorry to take up your time. &nbsp;Dave McA

nkal24 · Answer

I'm curious as to how you solved it. I would have tried using the formula for the derived column directly in SORTBY (instead of the derived column).

soproni99 · Answer

nkal24&nbsp;I’m not completely sure what did the trick, since I played around with it for so long. &nbsp;The columns referred to in the INCLUDE part of the FILTER are now amoung the set of columns returned by the filter. &nbsp;The INCLUDE part uses a formula referring to those included columns rather than to the data columns themselves. &nbsp;So I think your suggested approach is the reason it worked, though the formula is in the FILTER not in the SORT.&nbsp;The sorting now uses just SORT, not SORTBY; I learned how to use it for sorting on multiple columns here:&nbsp;https://exceljet.net/videos/sort-and-sortby-with-multiple-columns. &nbsp;&nbsp;I had some trouble using SORTBY; I could't figure out how to refer to the relevant columns when trying to use SORTBY; I don’t think referring to the actual data columns worked, but SORT enabled me to refer to the columns included in the array returned by the filter.&nbsp;I’m quite amateur so I have troubled doing anything at all fancy.

Forum Discussion

Sortby and Filter together - Circular Reference Error

8 Replies

Resources