Data Validation: Drop down lists

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.

Read More

Toolbar Crazy

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.

Read More

Data Filtering : Advanced Filter

The OOo Calc Advanced Filter is similar in functionality to the AutoFilter wherein lists are filtered according to a combination of multiple criteria.

What is different is the mechanism by which we define these criteria.

Sometimes, the Autofilter does not meet the requirements due to the limited number of criteria that can be applied. With Advanced Filter, the criteria are defined in the spreadsheet. This is illustrated below in cells B2:D4 The criteria matrix normally has the same colums and headers as the main database. Each row of the criteria matrix corresponds to a conditon to be applied to the database when filtering. Criteria in the same row are ANDed, while criteria in different rows are ORed

In the example below, we have configured the criteria to select rows whose Year is 2000 AND whose sales exceeds $5,000. We have yet to apply these criteria to the database.

The Advanced Filter dialog is opened with Data-FIlter-Advanced Filter – after selecting the database cells we wish to filter and having created our criteria matrix (see above)

In the Advanced Filter dialog, we just need to identify the criteria matrix and then press OK

The filtered data is shown below.

Finally, we see an example where the same original data is filtered with a different criteria matrix. Here, with the conditions on different rows, they are ORed.

Read More

Autocorrect

When entering text in OO Calc cells, you may notice that the program makes assumptions about what you are typing and you get unwanted corrections.

These corrections are completely configurable throught the AutoCorrect dialog.

The AutoCorrect dialog is invoked by selecting Tools – AutoCorrect as shown below.

The Options tab presents some optional corrections that OOo Calc can perform on any text entered by the user. From the identifier of each option, it should be easy to understand what OOo Calc plans to do – if that option is enabled.

In particular, the user can make use of a replacement table of common mispellings. The replacement table is fully configurable via the Replace tab – shown below.For example, if “acn” is a string you use often (a name of a company perhaps) OOo Calc will always insist on replacing it with “can”. By removing this particular entry from the replacement table, OOo Calc will leave “acn” alone.

The Exceptions tab – see below – allows the user to prevent OOo Calc from correcting certain 2-letter initial cap combinations.

Finally, the Custom Quotes tab allows the user to replace single/double quotation marks with any characters of his/her choosing.

Read More

Macros: Getting Cell Information

Invariably, macros written for use within the Calc application will need to access the contents of the cells on a spreadsheet. This tip is an introduction to the various available methods.

The three methods we will look at are getCellByPositiongetCellRangeByPosition and getCellRangeByName

The function that is first encountered for most people is getCellByPosition. In the sample below, we access cell A1 (on Sheet1)


Sub getCellInfo
‘get the first sheet of the spreadsheet doc
xSheet = ThisComponent.Sheets(0)

‘Get value of Cell A1
A1_value = xSheet.getCellByPosition(0,0).value

print A1_value

End Sub

The second example shows the use of getCellRangeByName and may be easier to use – because the cells are referenced by the traditional column/row identifiers that are displayed along each axis. However, for applications requiring looping through an array of cells, getCellByPosition is easier to use.

Sub getCellInfo
‘get the first sheet of the spreadsheet doc
xSheet = ThisComponent.Sheets(0)

‘Get value of Cell A3
A3_value = xSheet.getCellRangeByName(“A3”).value

print A3_value

End Sub


The next example shows how getCellInfo grabs an array of cells – myTable. A subsequent call to getCellByPosition for the myTable object is relative to the origin of this array.

Sub getCellInfoByRange
Dim myTable as Object

‘get the first sheet of the spreadsheet doc
xSheet = ThisComponent.Sheets(0)

‘Grab array A3:A5
myTable = xSheet.getCellRangeByName(“A3:A5”)
A5_value = myTable.getCellByPosition(0,2).value
print A5_value

End Sub

The final method that needs discussion is getCellRangeByPosition and the example below illustrates it’s use. It is equivalent in functionality to the previous example.

Sub getCellInfoByRange
Dim myTable as Object

‘get the first sheet of the spreadsheet doc
xSheet = ThisComponent.Sheets(0)

‘Grab array A3:A5
myTable = xSheet.getCellRangeByPosition(0,2,0,4)
A5_value = myTable.getCellByPosition(0,2).value
print A5_value

End Sub

Read More

Copying Formulas while preserving references

When you cut and paste formulas from a range of cells, the cell references within the formulas will be automatically adjusted. Here, we show how to work aroud this ‘feature’.

For the purposes of illustration – we consider the simple example below – with the four formulae in B7:E7

WIth B7:E7 selected, we select Edit – Find & Replace We will be replacing the “=” characters with “#” in each formula. Now, we have a range of text values that will be unchanged in a cut/paste or copy/pastee operation.

We can now copy/paste on the range of text values.

The last step is to use Find & Replace to restore the original formulae by replacing “#” with “=”, reversing the previous Find & Replace operation.

Read More

Filling a Cell Range with a Series

A quick way to fill a range of cells with a series of values that increase/decrease in a geometric fashion.

Let us consider the simple example below. We wish to complete the series of values atrting at B3 (shaded).

The Fill Series dialog is invoked with Edit – Fill – Series as shown below.

OOo Calc makes it’s best guess regarding the continuation of the series.

In this example, we will elave everything at the default settings and close the dialog with OK

The completed series! Next,we will look at handling dates.

Read More

Text manipulation 1 : Concatenation

There are two methods available for concatenating text.

The ampersand symol & can be used as a concatenation operator on text – as shown below. This is analogous to the + operator for numbers.

There is also a CONCATENATE function – which can be used on up to 30 strings. The CONCATENATE function is also shown in the example below.

Read More

Regression analysis I : Basic linear formulas

As requested by Joerg in Germany, I am going to cover the topic of regression analysis over the next few days – including formulas, built-in functions and charting with trendlines.

Consider a set of data point pairs – which suggests a possible linear relationship between the two variables.

The equations below are used to calculate the slope m and y-intercept point b for a given set of data, as well as the correlation coefficient r.

Given the formula above, it is a straightforward process to extract the linear coefficients for the given set of data points.

Next, we will look at buil-in OOo Calc functions and the plotting of the trendline for this linear regression example.

Read More

Rounding to the nearest nickel: MROUND()

The MROUND function allows you to round to any value you want and is particularly useful in financial/commerce applications where rounding to the nearest nickel/dime/quarter is required. This function is only available if Analysis AddIn is installed. It rounds the target value to any multiple that you specify.

To round the value in A1 to the nearest nickel we use =MROUND(A1; 0.05)

Unlike the equivalent Excel function, MROUND in Calc allows negative numbers.

In fact, MROUND() is so useful – it can even be used to round the time to the nearest quarter hour. An example of this is shown below.

Read More