I am trying to achieve a certain unusual result using Google Sheets and here's what I've got so far.

My formula takes the value of a randomly selected cell within the named range "IntroLines":

=INDEX(IntroLines,RANDBETWEEN(1,ROWS(IntroLines)),1)

The only problem with it is that some of the cells in that named range are blank and I would like NOT to select those cells. Any ideas how I could update this formula to enforce that a non-blank value is selected?

Note: I would strongly prefer to solve this problem without removing blank cells from the named range.

CodePudding user response：

try:

=INDEX(SORT(IF(IntroLines="",,{IntroLines, RANDARRAY(ROWS(IntroLines))}), 2, 1), 1, 1)