Forum Discussion
Sortby and Filter together - Circular Reference Error
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?
I managed to overcome the problem with some further effort. I’m sorry to take up your time. Dave McA
- 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).
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.
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.
