OpenOffice Calc Tips

There are a number of situations where it is desirable to take advantage of the sheet/cell protectection features of OOo Calc.

Perhaps you want to prevent the values for a range of cells from being overwritten. Perhaps, you want to protect sensitive financial formulae but share the results.

In order for protection settings to be enabled for a range of cells, the document or sheet must be protected. Protecting a sheet or document prevents the format of the cells from being changed - and because cell protection settings are part of the format, we apply this first before protecting the sheet.

By default, all cells are unprotected and all formulae are visible (below)

With the desired cells selected, open the Cells Format dialog - Cell Protection tab - shown below. Select Protected to prevent changes to the contents or the format of a cell. Select Hide Formula to hide formulas.

With the correct protection settings having been applied to the selected cells, we must now protect the sheet or document. This is done as shown below…

A password is required - don’t forget it!!

This is the message that you get when attempting to modify the contents of a cell.

This is a topic that you will best learn by trying out stuff for yourself - but here I summarize the basics…

There are a number of builtin functions that make use of regular expressions. Here, we use COUNTIF to illustrate some of the possibilities.

Regular expressions are a system for matching patterns in strings. They provide a very powerful set of tools for finding particular words or combinations of characters.

Based on my investigation, the following is a summary of the allowable constructs in OOo Calc.

• Most characters match themselves. There are exceptions - see below.
  
  COUNTIF(A1:A100; “foo”) will return all the instances of the string “foo” in the specified range.

• “.” matches any single character.
  
  COUNTIF(B2:B18; “..”) counts all cells with exactly two characters.

• “*” is a special character that matches zero or more occurences of the previous expression.
  
  COUNTIF(B2:B18; “.*e”) counts all cells that end in “e”.

• “+” is a special character that matches one or more occurences of the previous single character.
  
  COUNTIF(B2:B18; “.+m.+”) includes “Tmmy” and “name”, but not “my”.

• A string of characters enclosed in square brackets ([]) matches any one character in that string.

  COUNTIF(B2:B18; “[efg].*”) counts all cells starting with e, f, or g.

From the company that seems to do everything right…

Google Analytics allows you to take a closer look at the folks that visit your website.

One of the coolest features is the ability to create a distribution map - below is the visitors to openofficetips.com in the past two days…

So - to all the folks in Catalunya, Vladivostok, Hanoi, Trondheim, Madeira, Bermuda, Costa Rica, Honolulu, Cape Town, Moscow, Fairbanks, Jakarta, Singapore, etc, etc thanks for stopping by!

By default, OOo Calc will create a chart with the y-axis starting at 0 (zero). For most purposes, this is adequate, but it is quite straightforward to shift the axis up (or down) where desired.

For the Flash version of this tip, click here.

Consider the data set below, which we wish to chart. This has been covered before elsewhere.

The default chart. In this example, we will adjust the y-axis to start at 20.

We first select the chart for editing by double-left-clicking anywhere inside the chart area. A solid border should appear around the chart as shown below.

Now enter the y-axis editor with the right mouse button as shown below.

Make sure the scale tab is selected. Before we modify the axis scaling, we first deselect the automatic settings. The fields indicated by the red arrows need to be changed.

When you have completed the desired modifications to the y-axis settings, close the dialog and deselect the chart. The final modifed chart is shown below.

Consider the simple example below - a table with various columns of data. Any formulas we create would be much more readable if we assigned names to the columns of the table.

With the full table selected, choose Insert - Names - Create to open the Create Names dialog.

The Create Names dialog gives you four options for defining the table range names. In our example, we will name each column according to the top row of the table.

With our newly defined names associated with the table, we can rewrite our formulae into more meaningful expressions.

The SUBTITUTE function replaces a sequence of characters with a different sequence of characters in a text string.

The arguments to the functions are :
SUBSTITUTE(text, old_text, new_text [,instance_num])
where

• text The text for which you want to substitute characters.
• search_text The text you want to replace.
• new_text The text you want to replace search_text with.
• occurence Optional: The occurrence number you want to replace.

This function should be used when you want to replace all the instances of some text regardless of their location in the text. If “occurence” is left blank, every occurrence of “search_text” is changed to “new_text”. The SUBSTITUTE function is very similar to the REPLACE function.

See also this tip for a clever application of the SUBSTITUTE function.

On the right side of the statistics, you will find a hidden gem of a feature - unless you are a former Excel user.

Select an array of numbers and the sum of the numbers appears in the bar - which you may or may not have noticed. However, if you right-click on the bar, you will see a choice of basic statistical operations to choose from - neat!

In the example below, we have chosen to display the average of the selected range.

Another quick tip illustrating the versatility of the Paste Special function.

To insert an empty row into an existing table…

Step 1: Select and Copy a single empty cell in your table.

Step 2: Select the row above which you wish to insert an empty row - then select Edit - Paste Special - below…

The key setting has been circled. Existing selection is shifted down while blank cell is copied across the table

The end result is shown below.

I have plans to implement a Keno game in the OOo basic language - but first a simple Lotto number generator. If you go into the source, you can configure to your own requirements. Generating a random number between 1 and 49 is really simple. Making sure that number has not already been picked slightly complicates matters.

Download it here

Sub Lotto
Dim i
Dim j
Dim val
Dim match
oSheet = ThisComponent.Sheets(1)
For i = 1 To 6
oCell = oSheet.getCellByPosition(1,i)
Do
match = False
val = int(rnd()*49) + 1
For j = 1 To i
If val = oSheet.getCellByPosition(1,j).getValue() Then
match = True
End If
Next j
Loop Until (match = False)
oCell.setValue(val)
Next i
End Sub

To create a series of dates in a range, with just weekdays (Monday through Fridays), you first enter your starting date in a cell (B2, in the example below), and then enter the following formula in the cell below that cell.

=IF(OR(WEEKDAY(B2+1)=1;WEEKDAY(A4+1)=7);B2+3;B2+1)

Then use the Fill Down utility to fill out your entire series of dates.

The formula to display the day of the week we have already met…

=CHOOSE(WEEKDAY(B2);”Sun”;”Mon”;”Tue”;”Wed”;”Thu”;”Fri”;”Sat”)