Sortby and Filter together - Circular Reference Error

[Soproni99]

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.

[#2]

@Soproni99 

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?

[#3]

@HansVogelaar 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.  

[#4]

@HansVogelaar 

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

[#5]

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).

[#6]

@nkal24 

I’m not completely sure what did the trick, since I played around with it for so long.  The columns referred to in the INCLUDE part of the FILTER are now amoung the set of columns returned by the filter.  The INCLUDE part uses a formula referring to those included columns rather than to the data columns themselves.  So I think your suggested approach is the reason it worked, though the formula is in the FILTER not in the SORT.

The sorting now uses just SORT, not SORTBY; I learned how to use it for sorting on multiple columns here: https://exceljet.net/videos/sort-and-sortby-with-multiple-columns.  

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.

I’m quite amateur so I have troubled doing anything at all fancy.

[#7]

Thanks for sharing the your experience. I'm also sort of new to Excel, and I find the site you mentioned helpful. Being a able to use sources not in the dataset (but of the same size) is one of the main benefits of SORTBY I think.

[#8]

Well, my solution is really a failure, since I only brought the needed columns into the filtered array because my reference to them otherwise had not worked. You are right that the advantage of SORTBY was therefore wasted. If you are very curious I would share the workbook with you privately…. It has kind of a inane purpose so I don't want to unleash it publicly. Maybe you could do better with the formula that defeated me.

My sheet had room for the superfluous columns, but I put them in white font to make the data in them invisible to the user. I’m embarrassed — it took me a lot of time to reach that point of exasperation.

[#9]

No need to share this particular one. But in the future, I'd love to look at similar tasks where the data is not sensitive. Have a great day!