OpenOffice Calc Tips

It is straightforward to enhance the capabilities of OOo Calc with your home grown functions and features. In this tip, we add the ability to change the case of the text in a range of selected cells.(UPPER CASE, lower case or Proper Case).

As you can in the screenshot below, we have added a new top level menu item TextUtils with 3 new functions, UpperC, LowerC, and ProperC. The last function capitalizes the first character only.

We could have invoked these functions with Tools - Macros etc but from a user’s standpoint, it is much more convenient to invoke them the way we have implemented it.

Pretty straightforward - the results of applying UpperC to the selected cells.

First - the source code…

Creating the new menu entries is simple. Invoke the Menu Customization dialog with Tools - Customize as shown below…

Select the Menus tab. Add a new top level menu item - TextUtils and then a seperate entry for each of our new functions.

Here is the Add Commands dialog. We select the module in the left hand box and the macro subroutine on the right. By default, the menu entry is given the same name as the subroutine but hat can be changed.

In the next installment, we will add a dialog to enhance out new functionality even further.

Often, we need to sum up a set of numbers that occupy the same position over a range of sheets. This is straightforward as shown below.

We have a sheet for each month as well as a Totals sheet. For illustration we add up the SUM of the A1 cells for each sheet from January to April with the formula =SUM(January.A1:April.A1). Note that this is slightly different from the corresponding Excel formula - =SUM(January:April!A1)

A good source of hints and tips for OOo Calc can be the numerous Excel blogs. One of the best is from Dick Kusleika.

This formula for counting the number of unique items in a range of cells was one of three suggested by Dick - but the only one that ported successfully to OOo Calc.

(more…)

A simple macro that converts a numeric value to it’s English equivalent.

(more…)

In this tip, we build on our previous lesson by looking at one of the key new OOo 2.0 features related to the DataPilot.

The example table below has four fields, Sex (M/F), pay grade, state of residence , and salary.

We open the DataPilot dialog as before. For OOo 2.0, a new Page Field is available. We drag the headers into the various DataPilot fields as shown below.

The new Page field gives us a filter to control the data that is displayed/processed in the DataPilot table.

Below, we have returned to the DataPilot dialog and swapped the STATE and GRADE fields. We see the different options provided by the filter. A good illustration of the power and flexibility of the updated DataPilot feature in OOo Calc.

Version 2.0 of OOo Calc will feature significant improvements to the DataPlot functionality. For this reason, we will revisit this powerful feature over the next few days.

First, the basics…

(more…)

Here’s a silly trick to split your string into separate characters across a range of cells.

You just need to enter the formula in C2 and then copy across the row as shown.

At first glance, the TEXT function may not sound very useful. Here are a few simple applications.

In the top example, without the TEXT function, the total is not formatted correctlt (as a currency). However, the TEXT function allows us to apply the correct format to the number component of the string.

In the second example, we see how the TEXT function allows us to reformat existing data - such as dates.