I am working on an Excel spreadsheet to help me stay on top of some extensive vocabulary and grammar points. I want the first row to generate values from a random cell corresponding to cells in the same column in the chart directly below. I have input a formula from online but some of the cells show “#REF” when generating a random value. It shows in multiple cells at once. I have no idea why the formula does not work every time. I cannot seem to get control of the “#REF”. What is a good formula to use to generate a value from a random cell in a specific column? If the one I have is good, how can I fix the “#REF” issue??

Formula is: =INDEX($[COLUMN]$[ROW]:$[COLUM]$[ROW],RANDBETWEEN([TOP ROW],[BOTTOM ROW]))

Example: =INDEX($A$2:$A$15,RANDBETWEEN(2,15))

​I am working on an Excel spreadsheet to help me stay on top of some extensive vocabulary and grammar points. I want the first row to generate values from a random cell corresponding to cells in the same column in the chart directly below. I have input a formula from online but some of the cells show “#REF” when generating a random value. It shows in multiple cells at once. I have no idea why the formula does not work every time. I cannot seem to get control of the “#REF”. What is a good formula to use to generate a value from a random cell in a specific column? If the one I have is good, how can I fix the “#REF” issue??Formula is: =INDEX($[COLUMN]$[ROW]:$[COLUM]$[ROW],RANDBETWEEN([TOP ROW],[BOTTOM ROW]))Example: =INDEX($A$2:$A$15,RANDBETWEEN(2,15))  Read More

​