I could have sworn I had posted this already, but I think it was just included in a comment on this post.
http://openoffice.blogs.com/openoffice/2007/11/using-the-autof.html
Some people are having problems inserting data from a CSV or TXT file into a spreadheet, using Insert > Link to External Data.
http://www.openoffice.org/issues/show_bug.cgi?id=1834
If this doesn't work for you, try Insert > Sheet From File instead.
Life just isn't cut and dried. Sometimes when you're filtering you don't want to just say "give me all the people whose last name is Hanson." You want Hanson, Hansen, and Hansengaaardennn (those Dutch really go for the jawbreaker names).
You'd like to filter out everyone except those whose names contain "Hans".
Here's how to do that. Select the item in the Comparison Field from the dropdown list in the standard filter, then type what you want in the other field. Click More, and select Regular Expressions, then click OK.
|
Example of what you want |
What to enter in the Condition field |
Syntax for what to enter in the Value field |
Example of what to enter in the Value field |
|
Begins with Hans |
= |
^x.* |
^Hans.* (you can also skip the ^, I've found) |
|
Does not begin with Hans |
<> |
^x.* |
^Hans.* |
|
Ends with Hans |
= |
.*x$ |
.*Hans$ |
|
Contains Hans |
= |
.*x.* |
.*Hans.* |
|
Does not contain Hans |
<> |
.*x.* |
.*Hans.* |
Here are some examples. Let's say you want all names that start with Hans, but not all names that simply contain Hans.
Here's the data.
Select all the data, or just click in the headings, and choose Data > Filter > Standard Filter. Make the window look like this.
Click OK and you get this; Bob Montrahans is not included. (It's not because of the case.)
Here's a different example. I want names that DON'T CONTAIN the series of letters Hans.
Data
The window with the restrictions:
And the results.
Here's some information from the OOo wiki about regular expressions.
In the Standard Filter you only have three slots for info.
That's a bit limiting. So the Advanced Filter lets you enter up to 8 criteria.
Using the Advanced Filter
Here's your data. Click the image to see it larger.
Now, here's how you enter your critera. Copy your headings and paste them somewhere else in the spreadsheet. Then type the values you want. Click this image to see it larger. I've entered Fargo for the city, ND for the state, and =>5 for the Years of Service. Note that they are all on the same row. This means they are ANDed together.
Click in the data (not the criteria but the main data) and choose Data > Filter > Advanced Filter. In the window, click in the right-hand field and draw a box around the area where you typed the criteria. Click OK.
You'll see the results. Click the image to see it larger. ( Simon being the first name for both is just a coincidence.)
Now, if you want OR logic, just enter the values in your critera section on different rows, like this. Click to see a larger version of the image.
These are the corresponding results. Because of the OR, you get a lot more results. Click the image to see a bigger version.
Removing the Filter
To turn off the filter, it's the same as with the standard filter. Click in the filter results, and choose Data > Filter > Remove Filter.
The Autofilter is a quick way to restrict what you're looking for. If you need some more flexibility, though, you need to move on to the Standard filter or the Advanced filter.
Maybe you have people from 12 states and you want to see the ones from Ohio OR Montana OR New Jersey. Or you want to see people with five or more years of service. Or you want to see anyone with more than three overdue library books who is also from Denver, because you're traveling to Denver and you want to drop by their houses and scare the heck out of them in person.
These are a challenge for the AutoFilter, so you move on.
Here's how to use the Standard Filter. Let's say you've got this data.
Click somewhere in the data and choose Data > Filter > Standard Filter.
In the window, enter your data. Note that any ORs will open up the results more than you might expect. Here's a filter. Either from MT or OR, and with 5 or more years of service.
Here are the corresponding results. Note the person from MT with only 1 year of service, but there's no one from OR with fewer than 5 years of service. The logic is
"anyone from Montana"
or
"anyone from Oregon who also has 5 or more years of service."
As with the AutoFilter, you need to click in the filter results to take away the filter. If you don't click in the filter results, as shown, the Remove Filter option is dimmed.
Click in the filter results, as shown, and choose Data > Filter > Remove Filter to get rid of the standard filter.
Sooner or later, you're going to get a huge spreadsheet with way too much data to scan visually.
How do you, ahem, filter out what you don't want to see?
One way is to use the AutoFilter.
AutoFilter Basics
Let's say you've got this spreadsheet of employees.
You'd like to just take a look at those from Montana, or those with a particular number of years of service. Something like that.
Click somewhere in the data, and choose Data > Filter > AutoFilter.
You see arrows by all the headings.
Click on one of the arrows, and choose to view all records containing one of the values, or all records containing the top 10, i.e. the ten most frequently occurring values in that column.
Here are the results for selecting one value for one column.
If you choose another value in another column, then you get rows that have the selected value for BOTH columns.
Here, I get rows for people who are in Montana, AND in Kalispell. Which works out fine since Kalispell is a city in Montana.
However, if I choose to view records for people from Montana, and from Portland (a city in Maine and in Oregon but NOT in Montana), I get nothing.
To go back to viewing all the values, select All from the list.
Then you get to view all the records again, once you've selected All for any columns you restricted.
When you're done and want to get rid of the little arrows, click somewhere in the data, and choose Data > Filter > AutoFilter again. There'll be a checkmark and when you select AutoFilter, it will go away.
There's the data the way it was before you started.
Issues With AutoFilter
Here's where things get a little twitchy. What if you try to turn off the AutoFilter and you have not selected a cell somewhere within the AutoFilter results?
You get this.
When you get this, click OK and click somewhere in the data.
Then choose Data > Filter > AutoFilter again. You won't see the checkmark, but that's OK.
Then choose Data > Filter > AutoFilter yet again. This time you'll see the checkmark.
And then the arrows will disappear and you're back to normal.
I also recommend liberal use of the Undo feature, Ctrl Z or click the Undo icon. You can undo at least 20 and possibly more depending on how your system is set up.
Can You Delete Rows When in the AutoFilter Without Deleting the Intervening Data?
I'm glad you asked. Yes, you can. Here's a demo. Look at the range from row 15, Dan Montbatten, to row 20, Beth Jerlin. They're both from Montana. In between you've got Jon, Marcus, and Kyle.
I'm going to view only people from Montana, which includes Dan and Beth but excludes the three rows between.
Now I'm going to delete Dan and Beth.
And they go away. However, Jon, Marcus, and Kyle are still there.
If you spend all day in spreadsheets, sooner or later you want something to help you spot what's important or different. The motion study expert Frank Gilbreth told factories to paint parts different colors to help factory workers spot the right pieces more quickly; Calc has roughly equivalent features to help point out the different types of data you're working with.
Many of the settings are controlled here. Choose Tools > Options > OpenOffice.org Calc > General. Click the image to see it bigger.
Here's a sample spreadsheet, shown the usual way.
Here's what it looks like when, in the Display section of the Options window, you mark the Formulas option.
Here's what it looks like when you mark Shown References in Color. It means if you double-click a formula, the referenced cells are shown color coded.
And if you mark the Value Highlighting option, then formulas are shown in a different color than formulas.
And in the same options window on the left side, you can change the color of the borders between the cells from light gray to whatever you want. Here's what they look like in magenta.
There are also some options that help you see the relationships among your data; the Tools > Detective menu item.
Tracing precedents means, for the selected cell or cells, show other cells that are a step up in the calculation. For instance, the tax rate is used in the selected cell to figure out after tax monthly income, so it's a precedent.
Tracing dependents is the same, but the other way around. Monthly Income depends on the selected cell, total income.
And, if you've got some errors, the Trace Errors feature will show what other cells are involved in the error cell.
Here’s a summary of the features from the 2.3 new features list that I considered the most useful or important to write about. This page http://wiki.services.openoffice.org/wiki/New_Features_2.3 about the new features is an excellent guide, as well.
General
This is
convenient for anyone who prints to multiple printers, all over the
world. You can load or ignore the printer settings for your
documents. This means you don’t end up accidentally printing to
the printer in building 4 which is on the opposite side of the
country, just because you were on a business trip there last week
and that’s where you last printed your document.
If your document isn’t wider than the OpenOffice.org
window, then it will be centered in the window, not left-aligned.
Lots of locale information was added, for locations such as
Tagalog, Frisian, and Hausa.
Writer and Web
The HTML editor now has a preview feature. Choose File > Preview in Web Browser and the document opens in the default browser.
I love this feature. You know how when you get a hyperlink
but then want to retype it or reformat it, but clicking on it takes
you to the target of the link? No more. You can select hyperlinked
text all you want; you now have to Ctrl Click to open a link. This
is very nice.
The notes say that there is a new compatibility option on
Tools > Options > OpenOffice.org Writer > Compatibility: Do
Not Justify Alignment in Lines Ending With Manual Line Break.
However, I’m mentioning this because I couldn’t see it. The
illustration shows the compatibility options that are there.
When you open the Styles and Formatting window (Format >
Styles and Formatting), you can set what kinds of styles you wanted
to see: Applied, Custom, Automatic, etc. Previously, you had to
reset this every time you opened a new document or re-opened
OpenOffice.org. Now, thankfully, that category will stick. The
setting is saved per application. However, the choice you make for
Paragraph, Character, Frame, List, or Page doesn’t stick.
When you right-click on text, you used to see Default as one
of the options. Now you see Default Formatting, which is clearer.
(Default Formatting is a great way to just clear out any extraneous
formatting and apply the default style to the selected item.) This
is a very nice feature regardless of the text; for one thing, it’s
the best way to remove the hotlink from a URL.
A new export filter lets you export to MediaWiki format. Choose File > Export and select MediaWiki in the file format list.
Calc
This is a very, very smart change. By default, the
print options for Calc are now set to Print Only Selected Sheets and
Suppress Output of Empty Pages. If the Print Only Selected Sheets
option is enabled, the Calc page preview shows only the displayed
sheet and the message “There is nothing to print.” To change
these options, choose Tools > Options > OpenOffice.org Calc >
Print, or choose File > Print and click the Options
button.
Here’s another very smart change that will screw up
all my documentation. :) The SUM icon on the main Calc toolbar has
changed. Now you can select the range of numbers to add, click the
SUM icon, and get the total in the first cell below the selected
range. Phew. But if you liked it the old way, it still works that
way, too.
Graphics can be linked to macros. This should help with Excel
compatibility.
The Excel export filter now handles the cotangent functions COT, ACOT, COTH, and ACOTH.
Calc now supports inline matrix/array constants in formulas. An inline array is surrounded by curly braces '{' and '}'. Elements can be each a number (including negatives), a logical constant (TRUE, FALSE) or a literal string. See this link for more detail. http://sc.openoffice.org/servlets/ReadMsg?list=features&msgNo=230
You can now use dynamic ranges, rather than absolute ranges
defined with $, in lists in Data Validity. Choose Data >
Validity, and under the Criteria tab select Cell Range from the
list.
The GETPIVOTDATA function returns a result value from
a DataPilot table, so it can be used in a cell formula. 
Mail Merge, Databases, and Forms
The infamous checkbox on the print message when you
print a mail merge document, Do Not Show Warning Again, is gone.
Phew! See this blog http://openoffice.blogs.com/openoffice/2006/10/how_to_get_the_.html
for why that caused problems.
This is nice. When you choose File > Print with a mail
merge document, in the Mail Merge window, you can choose to save the
document as separate documents or as one document.
Unfortunately, in Base there is still no File > Export or File > Import feature. File > Export does appear, but it’s dimmed.
I have heard various statements or rumors about the Novell version of OpenOffice.org being able to convert Excel macros to OpenOffice.org format. Can anyone comment on this, with personal experience? It is of course a fabulous thing to be able to do, if it's possible.
Solveig
Thanks to Scott for tipping me off to this.
Here's something interesting. The question: what happens when contributors and, I'll just say publishers, want different things?
The result seems to be that Openoffice.org, as Linux did, is going to have different versions. Perhaps not for the same reasons.
Kohei Yoshida wrote a long post on the history of Calc Solver, which is an optimization
solver module for the Calc component of OpenOffice.org. After three
years, they don't agree on some aspects of the licensing. Now Michael Meeks has
announced ooo-build (previously just for build fixes) is now a formal
fork of OpenOffice to be
located at http://go-oo.org/.
The fork looks pretty interesting as it includes several things that have not make it into the official build.
Intrigue in the OpenOffice.org world. In ten years will there be as many versions of OOo as there are of Linux?
I've been blogging about sorting for a while:
There's just one more thing to mention: creating your own custom sort order.
Existing sort orders are things like Monday Tuesday Wednesday (the right order, which is not alphabetical). But let's say you've got things you want in a certain logical but non-alphabetical order that aren't already set up in OpenOffice.org: titles of books or people, procedures done in a certain order, or your own abbreviations for the days of the week. You can create sort orders for those very easily so you can sort by them.
Let's say you've got this data.
This is the right order. If you had them out of order, though, and wanted to sort them, all you'd have the option for is alphabetic. Which isn't right. So you create an order to sort them by.
Select just the content of the sort order, nothing else. Be sure it's in the order you want it.
Choose Tools > Options > OpenOffice.org Calc > Sort Lists.
Now just click Copy.
The list will appear. Click OK.
Now just sort the way you would with a custom sort order. You'll see your new sort order in the sort window.
Sort orders are nice for quick data entry, too. If you type the first word for a sort order, then find the small black handle in the lower right corner of the cell and drag, you'll be able to easily fill the cells with the rest of the content in the sort order.
Type the first word
Find the black handle and drag (magnified to show the handle)
The data will fill in each cell you drag through, in the order set up in the sort list.
All right. It's time to do some really advanced sorting.
Let's say you have a schedule and you want to sort it by day of the week. What's the first day of the week? Monday. But alphabetically it's not first. Friday, for instance, comes before Monday. So here's how to sort by days of the week, months, etc.
You've got your data.
Select it and choose Data > Sort.
Click the Options tab and make the appropriate selection for Range Contains Column Labels.
Now select Custom Sort Order and select the one you want.
Click the Sort Criteria tab and select the column to sort by that contains the corresponding kind of data.
Click OK.
And you get your results.
Wondering "Where did that sort order come from?"
Wondering "Could I perhaps make my own sort order, like President Vice-President Secretary Treasurer or High Card, Pair, Two Pair, Trips, and so on?"
You will have your questios answered in the next installment of Sorting.
You want to do some good, hard sorting. Not just sorting by the first column, but by perhaps the third column. Maybe you want to sort first by state, then by city, then by last name. For that, you need the Sort window under Data > Sort.
You have your data. Select all the data to sort, and either select the headings or not. You're good either way.
Choose Data > Sort.
Click the Options tab. You need to tell Calc whether you've got headings selected or not. If you've got headings selected, you want the option shown, "Range Contains Column Labels," to be selected. If you didn't, unmark it.
Now click the Sort Criteria tab. If you selected headings you'll see the headings themselves; if you didn't then you'll just see Column A, etc. Select the column to sort by, and Ascending or Descending.
Click OK.
You get your results. Here, the amounts under the column for the year 2000 are sorted in ascending order.
Now, let's look at a different set of data. You have a lot of people from the same state, and several cities per state. In this case you might want to just sort by last name, but you could also group by location. So you'd sort by state (the broadest category), then city, then alphabetically by last name. Click the image to see more detail.
Select the data, with or without headings, and choose Data > Sort. In the Options tab, be sure to select the Range Contains Column Labels option if you selected headings.
In the Sort Criteria tab, select first State, then City, then Last Name.
Click OK.
You get your results. Here's a closeup of one section, followed by the complete data. Click either to see them closeup.
This window that I've covered in this blog entry is pretty much what you need. If you want to go a little farther and sort by something else, like days of the week in the order they come, not alphabetical order, tune in for the next sorting blog.
In the mood of fall cleaning and to celebrate having gotten my office organized, I'm going to blog this week about sorting. Today, it's just basic run-of-the-mill sorting using the sorting icons on the toolbar.
Let's say you've just got this list, and you want to sort it alphabetically.
Select it, without selecting any data you don't want sorted.
Click either the A-Z or Z-A icon.
You get your results.
Be sure not to select any headings, i.e. the word Employee in this case, or you get this result, which you don't want.
What if you have something like this, though? Something with multiple columns.
Here's the thing. Selecting, say, the 2000 column and clicking a sort icon will NOT give you good results. It will sort just the data in the 2000 column and leave all the other data behind. So all of a sudden your data is wrong.
You cannot specify the column to sort by, using the simple sort icons. That's covered in my next blog entry on sorting.
Here's what you can do. You can sort by the first column in the data set. You select ALL the data, again without the headings.
You click the sort icon you want.
And you get your results; the data is sorted by the first column. That's your only choice.
If you had selected just one column, the data would be goofed up. 
For more control over sorting, tune in for the next sorting blog.
I just started using feature this recently, for my LinuxWorld presentation. It's very nice. Anytime you can reference some text, rather than embedding it in the formatting, it's good.
Some background: under Data > Validity, you can control what people can enter in spreadsheets, and offer them help in the form of lists, help tips, etc. 
I wrote about the Validity tools here
http://openoffice.blogs.com/openoffice/2007/01/openofficeorg_c.html
and elsewhere on my blogs. However, what I haven't talked about yet is how to make a dynamic list.
Let's say you've got a spreadsheet documenting the year's performance for the 120 people in your company, one sheet for each person. They're summarizing what they've done this year, and you want them to list the projects they've worked on. You would like to list the Official projects rather than having them type in whatever. So you want a dropdown list. Howeve, you don't want to have those projects typed in that dropdown list because then if you change it, or use this spreadsheet next year, you'll have to correct the list 120 times. (You can copy and paste, but still.)
So you make a list of the projects that references a cell range in a sheet called Projects, say, and all you need to do is update that one cell range to make all the lists update.
Here's how you do it.
1. Click in the cell where you want the list to appear. If you want the list in multiple cells, select multiple cells.
2. Choose Data > Validity.
3. Select Cell Range in the list.
4. Type the range. To type a range in the same sheet , type something like this:
$F$1:$F$20
You need the $ to make the reference absolute.
If the range is in a different sheet, add the absolute sheet reference in front, like this.
$Projects.$F$1:$F$20
5. Click OK.
Then if you need to, just change the contents of the cell range and the list updates. Previous entries in those spreadsheets containing entries no longer in the list do not change.
If you have a bunch of Microsoft Office templates that you love, you can just choose File > Open in OpenOffice.org to use them. However, if you want them to be available when you create a new presentation, or available from File > New > Templates and Documents in OpenOffice.org, you need to do two things: convert them to OOo format, and put them where OOo expects to find them.
Converting Your Microsoft Office Templates to OpenOffice.org/StarOffice Format
You need to convert them to OpenOffice
format. Choose File > Wizards > Document Converter. Follow the
wizard through to create a converted set of copies of the templates.
Step 1. 
Step 2, then just follow the wizard as prompted.
Thanks to Huw for the tips. For information on the sites, see:
http://www.openoffice.org/issues/show_bug.cgi?id=33851
http://wiki.services.openoffice.org/wiki/Calc/Drafts/Issue_33851
http://wiki.services.openoffice.org/wiki/Talk:Calc/Drafts/Issue_33851
Let's say you've got a big list of employees: name, address, etc. When you apply the filter and view, for instance, only people from Montana, some of the data isn’t shown. If you then copy, paste, delete, or perform other operations on the data, what happens to the data that isn’t shown? If you delete Artie Anderson from Montana and Cindy Chalmers from Montana, what happens to Betsy Bates from Nevada?
It all depends on the operation. Some, like delete, leave the unshown data alone. Some do affect the unshown data.
Operations that DO affect filtered out rows.
Cut and Paste
Move (dragging)
Fille (Edit > Fill or dragging)
Operations which do NOT affect filtered out rows:
Copy
Delete contents
Delete row
Format
Find & Replace in current selection
More About Operations that DO Affect Filtered-Out Rows
Cut and Paste, Versus Copy and Paste
With Cut and Paste, the non-shown data is cut and also pasted.
Click the following to see a bigger image.When you paste the data outside of the filter range, the whole set of data is pasted and unaffected anymore by the filter.
When you paste the data inside the filter range, the whole set of data is pasted, and all data is shown at first, even data that shouldn’t show for the current filter selections. However, if you re-apply the filter selections using the dropdown lists in the heading row, then the data is filtered correctly.
With Copy and Paste, the behavior is different. If you cut, you get the nonshown rows, but if you copy, you don’t.
Dragging Cells to Move Them
When you move rows (dragging) that include unshown rows, the behavior is the same as cut and paste. The unshown rows between shown rows are moved along with the shown rows.
Fill (Edit > Fill or Dragging the Cell Handle)
Here’s what happens with Fill. Here’s some sample data, and currently everyone is in the same department.
You now look at only people from Colorado.
You change the department for the first person from Colorado, and drag that department down through all the other people from Colorado.
Now, all the nonshown rows after the first row you changed are affected, but not the rows before that.
More About Operations that Don’t Affect Filtered-Out Rows
Copying and pasting, deleting, formatting, and Find and Replace don’t affect unshown data. The following section provides an example.
Deleting
Here’s a walkthrough of deleting rows while the filter is on. Rows that aren’t shown aren’t affected.
Look at the range from row 15, Dan Montbatten, to row 20, Beth Jerlin. Dan and Beth are both from Montana. In between are Jon, Marcus, and Kyle.
The next illustration shows an AutoFilter with only people from Montana, which includes Dan and Beth but excludes the three rows between.
Now delete Dan and Beth.
And they go away. However, when the state autofilter criterion is removed, Jon, Marcus, and Kyle are still there.