So I'm making a movie watchlist in google sheets and as a super indecisive person i would like to be able to pull one movie from the list to watch at random. But exclude any that I have seen, as indicated by the tick box in front of the movie. I'm kinda struggling with the excluding part in both formulas and scripts, so any help would be appreciated! :D
Here's a copy of the sheet if it would help anyone willing to help, but basically I would like the randomly pulled movie to show in I5!
Tried with the INDEX and RAND(something) formula but it still shows #N/A when it generates from a row that's checkmarked.
To get a random movie from the list that you have not seen yet you could try this formula:
=LET(movies, FILTER(C3:C, NOT(B3:B)), SORTN(movies, 1, 1, RANDARRAY(ROWS(movies)), true))
It will return a random movie on every change in the spreadsheet.