Hi All,
Firstly thanks in advance for the free advice - its much appreciated. So I have a main list of made up of names, Teams and Other text. The list is 100 rows but may not always be fully populated (e.g. sometimes it have 50 rows, the following month it may have 100), so some rows could be blank. The maximum population of this list will never exceed 100. In a separate list I want to retrieve 3 random names from the main list.
I've tried this formula without success: =INDEX($D:$D,INDEX(MODE.MULT(IF($D$8:$D$100<>{"",""},ROW($D$8:$D$100))),RANDBETWEEN(1,SUM(N(LEN($D$8:$D$100)>0)))))
Where D is the column in the main list with Names. Problem: This formula quite often produces duplicates, or blank cells.
Not sure where I am going wrong with this one. I have attached screenshots of how the lists are made up if that helps.