4

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...

  1. Make it display the name of the film
  2. Automatically detect the top number as I add new DVD’s so I don’t have to hard code it into the formula?
  3. 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.

4 Answers 4

6
  1. To return the value from column A, row R, you can use =INDIRECT("A"&R)

  2. To count the number of values in column A, you can use =COUNTA(A1:A65536)

    So for your case, you could use =INDIRECT("A"&RANDBETWEEN(1;COUNTA(A1:A65536)))

  3. You might be able to make a macro button that will recalculate for you. I don't know enough about OpenOffice.org Calc's macro language to provide more help there.

0
0

I'm not familiar enough with the internals of OpenOffice to help with with your third request, other than to say that just plain F9 works for me, no shift or control needed. As for the other two, though:

=INDIRECT(ADDRESS(RANDBETWEEN(1,MATCH("*",$A$1:$A$50000,-1)),1))

Replace the 50000 with something bigger if you have or think you will have more than 50000 DVD's, and replace the 1 in the ADDRESS() function if it is in a column other than A.

2
  • OpenOffice.org Calc requires semicolons between arguments, commas don't work.
    – Bavi_H
    Jan 30, 2011 at 18:45
  • Perhaps it's because I was using LibreOffice instead; it works just fine here, and is a direct copy from LibO. Jan 30, 2011 at 19:32
0
  • Put the formula from Bavi_H's answer in cell B1 (=INDIRECT("A"&RANDBETWEEN(1;COUNTA(A1:A65536))))
  • Make the width and height of B1 nice and big
  • Format B1 with an attractive font in a large size, chosen so that long movie titles will still fit
  • Select any cell in column A and select the menu item Format > Column > Hide
  • Select the Tools > Macros > Record Macros menu item
  • Press F9 (or Ctrl-Shift-F9)
  • Click on the Stop Recording button
  • In the Basic Macros Dialog that appears, type a name such as "refresh" in the Macro name field, select the spreadsheet in the Save macro in box, click Save and enter a module name such as "movies", click OK
  • Select the menu item View > Toolbars > Form Controls
  • In that toolbar, make sure the Design Mode button is turned on
  • Press the Form Design button to make the Form Design toolbar visible
  • Within the Form Design toolbar, turn off the Open in Design Mode button
  • In the Form Controls toolbar, click on the Label Field button
  • Drag the cursor from the upper left corner of cell B1 to its lower right corner to create a label field that's the same size as the cell
  • Right click on the cell/label and select the Control... item from the context menu
  • Select the General tab, then delete the text from the Label field
  • Select the Events tab, click on the ... button
  • In the Assign Action dialog box, click on the Macro... button
  • In the Macro Selector dialog box, expand the entries under your spreadsheet in the Library box until you see the module name you used earlier (e.g. "movies") and click on it
  • In the Macro name box, select the macro you created earlier (e.g. "refresh")
  • Click OK, click OK then close the Properties box by clicking on the X button in its upper right corner
  • In the Form Controls toolbar, turn off Design Mode
  • Now when you click on cell B1 the movie name displayed there should change
  • Close the toolbars if you want
  • Before you save the spreadsheet, you could go into Tools > Options > OpenOffice.org Calc > View and turn off Window: Column Headers and Rows, Horizontal Scroll Bars, Vertical Scroll Bars and Sheet tabs for a very clean looking appearance (you may need to turn these back on the next time you create a new blank spreadsheet)
  • Save your spreadsheet
  • ???
  • Profit

Movie Chooser

0

To make it easy, number the DVDs in column A and put the names of the DVDs in column B (you can use the formula A2 = A1 + 1 to number if you want to make it even easier).

Assume n = the number of rows above where the DVDs begin, such as if you have column labels.

=INDEX(B:B, RANDBETWEEN(1, MAX(A:A)) + n, 1) 
1
  • OpenOffice.org Calc doesn't seem to allow the A:A method of referring to a column, you have to use A1:A65536. Also, Calc requires semicolons between arguments, commas don't work.
    – Bavi_H
    Jan 30, 2011 at 18:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .