To select all the cells of a sheet, click in the space above the 1 and to the left of A – see below.
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.
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.
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.
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.
We have already seen a table lookup example using the OFFSET function.
Here, we perform a similar operation using INDEX and MATCH
The example below show how well INDEX and MATCH can work together.
MATCH will return the position of a particular value in an array, while INDEX returns the value of a particular position within an array. We see multiple examples of these functions in operation below.
PriceMatrix is C4:E6 Material is C3:E3 Item is B4:B6
Using these names in the formulas below makes for easier readability nad maintenance. For this reason, it is always good practice to take advantage of the ability to name arrays that will be used within formulas.