The core problem here isn't the filter function, it's the assumption that manual input can coexist peacefully with dynamic data. You've built a system where the data flows automatically based on user answers, which is exactly the right instinct. But then you introduced a static manual column on the Recommended tab, and that's where the logic breaks. The moment a user changes an answer on the Questions tab, the filtered results shift, and the Yes/No selections become orphaned. They no longer correspond to the correct rows. This is not a limitation of the tool; it is a design conflict.
The practical solution is to stop treating the manual input as a separate column and instead embed the user's choice into the data itself. Move the Yes/No selection onto the Formulas tab, the hidden source of truth. Give each item a dedicated column where the user can mark it as required or not, and then let your filter logic pull that column along with the rest of the data. When the Questions tab changes and the filter reshuffles the rows, the user's manual selection stays attached to the correct item because it lives at the source, not in the filtered output. This keeps your Recommended tab clean and accurate without requiring the user to re-enter their preferences every time the questions change.
We believe this approach respects the user's effort while honoring the dynamic nature of the spreadsheet. It shifts the burden from remembering which row is which to trusting the system to maintain alignment. The alternative, trying to make the manual column "dynamically update" alongside the filter, is chasing a workaround that will only grow more fragile as the spreadsheet scales. You've already built the hard part: the logic that moves data based on answers. Now finish the job by making the manual input part of that logic, not an exception to it.