I have a list of my (279) DVD’s in an OpenOffice Spreadsheet (version 3.3.0) on my Windows 7 laptop and I want to create a formula to randomly pick a film to watch (for occasions when I can’t make up my mind). This works:
=RANDBETWEEN(1;279)
But all it gives is a number that I have to then scroll up and down to check and it also has the total number of DVD’s ‘hard coded’. I want to display the name of the film (held in column A) so that if, for example, the number was 277, the name of the film would appear, in this case ‘X-Men 2’. I assumed that just adding the column letter in the front like this:
=A(RANDBETWEEN(1;279))
would work but it throws up an error: #NAME?
So, how can I...
- Make it display the name of the film
- Automatically detect the top number as I add new DVD’s so I don’t have to hard code it into the formula?
- How can I make it so that I just click the cell to regenerate a new random number? (i.e. not have to recalculate manually by pressing Shift+Ctrl+F9)
Thanks.