Forum Discussion

Soproni99's avatar
Soproni99
Copper Contributor
Aug 05, 2024

Sortby and Filter together - Circular Reference Error

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.

 

8 Replies

  • 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?

    • Soproni99's avatar
      Soproni99
      Copper Contributor

      HansVogelaar 

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

      • nkal24's avatar
        nkal24
        Copper Contributor
        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's avatar
      Soproni99
      Copper Contributor

      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.  

Resources