Looking up data in tables V

Here is another simple application of the VLOOKUP function.
Consider a sales person who gets paid commissionson sales based on a sliding scale.

For sales up to $50, a comission of 25% is paid, between $50 and $100, the commission drops to 22.5% – and so on. By using the lookup table and VLOOKUP, the correct rate of commission can be applied to each sale.

Read More

Conditional Formatting I

Conditional formatting is a mechanism that allows the user to apply formatting to a cell or range of cells based on defined conditions. If the rules (conditions) are met, then the specified formatting is applied. In OOo Calc, you can apply up to 3 rules in a cell or range of cells.

In the example below, we have an array of numbers. To illustrate how conditional formatting works, we will apply different formatting to those cells whose values are greater than the average of the range.

We first need to define a new formatting style.

Apply the desired formatting to an isolated cell and invoke the Styles and Formatting dialog as shown below.

Here, we click on the New Style from Selection icon.

This allows us to add our own custom style to the Style catalog.

We will now make use of our newly defined style in the Conditional Formatting dialog, which is enabled as shown below.

We select Condition 1 and the settings are shown below. The Cell STyle drop down list allows to to select any of the styles from the catalog – including any custom style we have defined.

You must use absolute addressing when referencing cells and cell ranges.

The final result of applying conditional formatting on the selected range of cells.

Read More

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

Charting: Editing charts : part 1

In this series of tutorial, we will take a newly created chart and make use of the various chart editing tools provided by OOo Calc to adjust the appearance of the chart to our satisfaction.

In the example below, the chart has just been created from the data in B2:C11 However, we would like to make some changes – making the chart background transparent, adjusting the orientation of the x-axis labels, changing the format of the y-axis numbers and correcting the label on the right hand side.

The first task is the chart background. We would like to make it transparent. We first select the chart by double (left) clicking inside it’s borders. Then we invoke the Chart Area dialog by (right) clicking and selecting Chart Area as shown below.

With the Transparency tab selected, we make the desired change as shown below. Click OK

The end result is much more pleasing to the eye. But we are not finished yet with our chart modifications. Next, we will adjust the orientation of the x-axis labels.

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