OpenOffice Calc Tips

Here is a useful formula - a long with a variant if you need to
enter dates quickly without the delimiters between the days, months and years.

In the example below, we enter our shorthand dates in column B. This column is formatted as Text so that any leading zeroes are not discarded by OOo Calc as it interprets the numbers entered as an integer.

Column C is formatted as a Date and the formula entered in C2 is

=DATEVALUE(TEXT(CONCATENATE(LEFT(B2;2);”/”;MID(B2;3;2);”/”;RIGHT(B2;2));”MM/DD/YY”))

Instead of using the CONCATENATE function, you can use the corresponding “&” operator.

=DATEVALUE(TEXT(LEFT(B3;2)&”/”&MID(B3;3;2)&”/”&RIGHT(B3;2);”MM/DD/YY”))

In the spreadsheet below, we wish to apply a formula to the values of a particular column - C and display the results in column D. However, the column is ’sparse’ in that not all cells in the column have values.

This would be problematic if the formula returns an error when applied to a blank cell. However, by incorporating the ISBLANK function
as shown below, we can gurantee that only non-blank entries are processed.

I’m not a big user of the OpenOffice Calc toolbars - but I was curious what it would look like if I activated them all…

You can enable/disable a particular toolbar with the View - Toolbars menu option. As you can see, it can get quite crowded if you anable them all! In my next installment, I’ll introduce you to some of them.

By combining INDEX and MATCH functions as shown below, we can create a lookup table whose data can be accessed by specifying the desired row and column values.

This is probably best applied where the lookup table is of fixed size - for example - calculation of sales commisions.

For tables that change on a regular basis - thus requiring constant updates of the lookup formulae, the Base application might be a better solution. Now if only I had more time to play around with that…

Here is a similar

example

I have been wanting to do this for the longest time - ever since I became aware of this puzzle last year. So for the past week, I’ve been busy putting together this spreadsheet that will assist you with solving a Sudoku puzzle.

Anyway, three days and 900 formulae later, I present… ooo_sodoku.

This is not a push-button solver. Perhaps the next version will allow that. Basically, for each square in the solution grid, the solver will determine all the possible values - saving you a lot of the drudge work associated with these puzzles.

As you manually enter the values in the solution grid - top left, the main grid in the center will display one of three colors for each square. Light blue indicates that square already filled in solution grid. Green indicates only one possible value - which has yet to be entered in the solution grid, and brown indicates more than one solution

Your comments, encouragement and feedback are always welcome. As I said earlier, there are 900+ formulae here - so bugs are possible.

Download this utility here

For each cell, you can define in advance what type of contents is valid for that cell. This allows you to guide users through data entry in OpenOffice.org Calc by restricting cells to receive specific values and ranges.

For selected cells, a drop-down list of possible values can be defined. In this tutorial, we show how this is done.

Step1: Select the required cell and open the data validation dialog as shown below.

Step2: In the Criteria tab of the validation dialog, the Allow option is itself a drop-down list. We select List.

Step3: With List slected, a text box appears which we populate with the list of allowed values for the selected cell(s). We are almost done.

After closing the validation dialog, we see that we now must select a value for our cell from the drop-down list we have just created - as shown below. It is also acceptable to leave it blank.

This example can be downloaded here.