OpenOffice.org Training, Tips, and Ideas

Data type issues in spreadsheet-based mail merges in OpenOffice, or why your mail merge doesn't work with 3.2

Let's say you're putting together a mail merge listing the fees people in the county have to pay for getting their new cat license. You'd put together the spreadsheet data kind of like this, then create a database based on that spreadsheet.

Name    Fee

Bob      $12

Jean    $13

Your mail merge document, with the various prompts and the field names, would look kind of like this:

Name: <Name>

Fee for license: <Fee>

So far so good. But let's say that sometimes there's no fee, perhaps organizations don't need to pay for cat licenses. So your data might look like this:

Name                          Fee

Bob                             $12

Jean                            $13

Library                         N/A

BUT that will not work because mail merges as of 3.2 (possibly earlier) don't like you to mix your types of data in a column. You can have all text, like N/A, or all numbers like 12, but not both.

Here's an example of some test and screen shots I took.

Here's the spreadsheet.

Here's what is in the database, and therefore what a mail merge would have access to.

 

So: in the scenario I outlined you could:

just put in 0, or just blank, instead of N/A

OR do this, which is workable but a bit more complicated.

Name                          Fee        Applicable or Not

Bob                             $12      [leave blank] 

Jean                            $13      [leave blank] 

Library                  [leave blank]       N/A

Then for your mail merge put both the fee and the applicable or not fields next to the Fee prompt. If there's no data the field won't take up any room, and only the correct field will show.

Name: <Name>

Fee for license: <Fee><Applicable or Not>

Note: This change in 3.2 really is a proper approach to databases; having mixed types is not proper database structure. But it is probably causing a few headaches.

How Various AutoFilter Operations Work on Data Not Currently Shown in OpenOffice Calc

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

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.

• 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.

Click the following to see a bigger image.

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.

 

 

---

---

Issues with Insert External Data

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.

---

---

Using regular expressions in OpenOffice Calc filters -- phrases like "contains" rather than equals, less than, etc.

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.

---

---

Using the Advanced Filter

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.

---

---

Using the Standard 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.

---

---

Using the AutoFilter: A useful if slightly twitchy tool

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.

 

---

---

Things that control how content is displayed in OpenOffice.org Calc spreadsheets

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.

---

---

Summary of new features in OpenOffice.org 2.3

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.

---

---

Question: Novell and Excel Macros?

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

Sun, StarOffice, Calc, and Forks

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?

---

---

Creating your own order to sort with: leaving alphanumeric in the dust

I've been blogging about sorting for a while:

• Easy sorting
• Standard, more powerful sorting
• Sorting by a custom sort order

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.

Sorting: Sorting by months or days of the week, in date order instead of alphabetical order

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.

---

---

Sorting: More powerful sorting using the Sort window

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.

Sorting: Your basic everyday sorting

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.

---

---

Creating a Dropdown List in Calc That References a Range of Values Elsewhere in the Spreadsheet

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.

---

---

Taking Your Microsoft Office Templates, and Making Them Available in OpenOffice.org or StarOffice

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 in the wizard 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.

Pointing to Templates to Use: Approach 1
(Use Approach 2 if this doesn't work)
Now, choose Tools > Options > OpenOffice.org > Paths. Select the Templates line.

Click Edit, then click Add, and add the path to the templates you want to point to. (Click this image to see a larger version if you like.)

Click OK, and OK, and OK  again.

Now you'll see the templates when you choose File > New > Templates and Documents.

Pointing to Templates to Use: Approach 2 (Usually Need to Do This for Impress)
1. Copy the directory of converted copies.
2. Paste it into this directory:
openoffice\share\template\en-us (or whatever your language is)
3. If they don't show up in the wizard, restart OpenOffice.org and your computer if necessary

If You Only See One of the PowerPoint Templates That You Converted, in the OpenOffice Wizard
See this entry.

For More Information on Templates
For more information on templates, see this post, which includes this information.

---

---

Search this blog:

» Blogs that link here

Opening CSV and .TXT delimited files as spreadsheets in OpenOffice.org Calc

How do you open this

as this?

If you just choose File > Open, a text file will open in Writer.

Here's how to open it as a spreadsheet.

Choose File > Open.

In the File Type list, select Text CSV. The easiest thing is to click in the file type list and type T four times.

Then select the file you want and click Open.

You'll see this window. It might not look right at first.

So be sure to select the right delimiter (and deselect the wrong ones) in the Separated By section.

If it's a fixed-width file, select Fixed Width then drag the field separator to the right spots.

Click OK. You'll see the file in spreadsheet format.

---

---

Correcting a Small Annoyance: OpenOffice Calc Spreadsheet Cells Don't Wrap By Default, Plus Making a Spreadsheet Template

Me, I like cell contents to wrap.

This is wrapping.

This is not wrapping.

This is more not wrapping.

So, wrapping is nice.

You can make cell contents wrap by selecting one, two, five, or all the cells, then choosing Format > Cells, clicking the Alignment tab, and selecting the Wrap Text Automatically option.

But then, when you cut the contents out of a wrapped cell, those wrapping attributes aren't applied any longer. This is a small point but one I find annoying. Look at this first example, where I applied the Wrap formatting to alllll cells in the spreadsheet.

Now here I cut out the middle item, paste it to the right, and type in the empty cell. The Wrap formatting left along with the contents.

Correcting the Problem
There are a few ways to get around this. Here's one way. Just redefine the cell Default Cell Style to be wrapped.

- Choose Format > Styles and Formatting.
- Right-click on Default and choose Modify.

- In the Alignment tab, select Wrap Text Automatically.

- Click OK.

Now forever and ever, for that spreadsheet, all cells will wrap period. (You can overwrite them on a cell by cell basis if you like.)

If you want to set up allllll  new spreadsheets to be like this so you don't need to redefine the default style for every new spreadsheet, do this.

- Create a new spreadsheet.
- Define the default style as wrapping as shown in the last set of instructions. Before you click OK, set up any other formatting that you want to always be there by default. For instance, you might want the font to be different.

Or you might want the number format to be always a specific, different format.

- Click OK.

- Choose File > Templates > Save.

- Select the My Templates category (or another if you want) and name the template something obvious.

- Click OK.

- Choose File > Templates > Organize.

- In the left-hand pane, open the category you choose previously when saving the template. Find the template. Right-click on it and choose Set as Default Template.

- Click Close.

Now, when you create a new spreadsheet, it'll be based on that template you created: cells will all wrap, they'll all have the font you wanted, etc.

To go back to the regular way of creating new spreadsheets, just choose File > Templates > Organize again, right-click on the template, choose Reset Default Template > Spreadsheet.

---

---

Useful OpenOffice Calc Formulas and Related Tricks

First of all: if you want some great tips on using OpenOffice.org formulas, go to www.openofficetips.com or http://www.richhillsoftware.com/  (same site).

But here are some that I think are useful: formulas, related items, and calculations built into Edit > Paste Special.

FORMULA -- you can display the formula for any cell.   

=FORMULA(B6) shows you the formula from B6. 

To display formulas by other means, choose Tools > Options > OpenOffice.org Calc > View, and under the Display area, choose Formulas.

To copy and paste only cells that contain  formulas, copy, then choose Edit > Paste Special. Select only Formulas and click OK.

Paste Special, using the Operations section.

You can add, subtract, multiply, or divide values of one set of cells with another.

Here's an example of adding.
- You have two sets of cells.

- Copy a set of cells.

- Select the other set of cells and choose Paste Special.

- In the window that appears, choose All in the left column, then under Operations choose Add.

- Click OK.
- The cells you copied are added to the cells you pasted.

Here's what before and after for division look like. The second set of numbers, that you paste onto, are divided by the ones you copied.

Copy one set


The second set is divided by the first set

ISBLANK returns TRUE if a cell is blank.

This isn't as flashy, but ISBLANK(cellreference) or ISBLANK(cellrange) can be used for error checking or just seeing if you've got blanks where you shouldn't. For a cell range, it would be something like =ISBLANK(A5:A55).

INFO is a slightly obscure but perhaps useful formula.

Here are the possible values. Click the F(x) icon on the main toolbar, double-click the INFO formula and you'll see this window.

Here are some example values.

 

---

---

Dropdown lists in OpenOffice.org Calc

Dropdown lists are a mainstay of many spreadsheets and forms. They help you control what people can enter, as well as giving them ideas for what types of things they might say.

You can make dropdown lists in a couple ways, at least, in OpenOffice.org. The simplest way is to use the tools accessed under Data > Validity. The lists are easy to make, plus you can easily use the values in formulas. In the following illustration, the value in B2, selected from a dropdown list, is referenceable in another cell.

Another way you can use the values in the dropdown list (among many) is to concatenate them. Click the following illustration to see it larger. The formula

=CONCATENATE(B2;" is an excellent source of information.")

sucks in the text in the dropdown list in B2.

So how do you make this list? I'll tell you.

1. Click in the cell where you want the list.

2. Choose Data > Validity.

3. In the list at the top, choose List as the type of information.

4. Select the various options you want.

- Allow blank cells: just what it sounds like. This means you don't have to select a value from the list, but if you type even one blank space, you can get the error message. More on that later.
- Show selection list: you definitely want this. What's the point of a list if you don't see it? Plus, then it's hard to figure out which cell has the list, to go back and modify it. (To modify an existing list, just select it and choose Data > Validity again.)
- Sort entries ascending: alphabetical order sorting. If you don't mark this, the items appear in the order you entered them.

5. Type the items you want in the list. Just press Enter to get to each new line.

6. Click OK. You'll see the list. Select something from the list. However, note that you can still type something that's not in the list, in addition to selecting something in the list.

7. If you want to require that people only enter something from the list, you need to do one more step. (You didn't have to click OK at that point; I just had you do that to show you the effect of not setting an error alert.) Select the cell where the dropdown list is and choose Data > Validity again.

8. Select the Error Alert tab.

9. You need to select the Show Error Message checkbox, select an error type (Stop is what you want to enforce selection of an item from the list), and optionally type the error title and error content.

10. Now click OK again.

11. Now when you try to type something different in the list, you get the error message you created.

---

---

How the New OpenOffice Chart Tool Works When You Don't Specify a Data Range

I've talked about the new chart tool in previous blogs here and here.

In both of those, though, I assumed that you would be selecting the data range in the Calc spreadsheet or Writer table before inserting the chart. Which you normally would be.

 

If you choose Insert > Chart in Calc, in the new tool, without a range selected, it's not much use. You get a big blank chart.

In Writer, however, at least in this stage of development of the tool, you get something different when you choose Insert > Object > Chart with no data range selected. Thanks to Linda from Largo for pointing this out.

It's not much good like that, of course, so right-click on it and choose Chart Data Table to input some data.

You get this window where you can not only fill in the names on the X and Y axes and the data, but you can add and remove rows and columns of data, and move rows and columns around.

This is nice if you have the data written down somewhere but not in a chart or table, and it's just quicker to slam in the data manually than to base it on a chart or table. It took about twenty seconds to make this chart. (It would have taken more if I'd had any actual data on penguins and marmots as pets. ;>  )

That was the chart data table, and then I just clicked the red X in the upper right corner to close and update the chart. Here's what it looks like.

This chart approach does lack titles, and X and Y axis titles. However, you can just make them with text boxes. Choose View > Toolbars > Drawing (deselect the chart first) and use the Text tool to draw a text box outside of the chart area. Type what you want in the text box, then drag it into the chart area.

In this example, I also clicked on and shrank the inner data part of the chart to leave room for the title I created with the text tool.

---

---

Calc Basics: Never Type When You Can Drag

If you're new to spreadsheets, or want to make sure you're working as efficiently as possible, this post is for you.

Whenever you have a lot of similar entries to make, think drag.

Here's an example.

You've got these budget figures for 2006. The 2007 budget figures are going to be 15% more.

So you create a formula for cell C2 multiplying B2 by 1.15 to get the correct figure for C2.

There's the result, and it's correct.

But it can get really tiring, and you can potentially introduce errors, by retyping formulas like this for every additional cell. You could type the same formula, with the reference to B3, B4, B5, etc. but that would be a lot of work and not efficient.

So instead, do this. Click on the cell where you already have the correct formula, and find the tiny handle in the lower right corner. Click and hold down your mouse on that handle, and drag down through all the cells where you want that same formula.

Here's what it looks like when you're dragging, before you release the mouse.

And here's what it looks like when you release the mouse.

All the formulas and results are correct, as you can see by clicking in one of the cells.

You Can Drag Things Other Than Formulas

You can drag words like the days of the week and months.

Drag

and release

You can drag numbers.

Drag

and release.

You can set up a specific increment, like 1 1 or 1 3 5 or whatever you want, then select all the cells in the pattern and drag, then release.

Set up the pattern

then drag and release.

If you drag a word that the system doesn't recognize, then you just get a bunch of those words, which might be what you want in some circumstances.

So whenever you want to repeat something, or increment a number or standard value like a month or day, Think Drag.

---

---

Using the Navigator to get around in an OpenOffice Calc spreadsheet

I've been talking about using the Navigator in Writer. Here are a few ways to use the Navigator in OpenOffice.org Calc spreadsheets.

Press F5, and you'll see the Navigator. It's got sections for the different standard things you might want to look for.  I'm going over the navigation items this time rather than the dragging and database items.

Click the graphic to see a bigger version.

Switching Sheets
If you have a lot of sheets, you might prefer to switch between using the Navigator's list. Just double-click on a sheet to go to that sheet.

Range Names
This is just a way to name a particular range of one or more cells.  To create the ranges, just select the cells and choose Insert > Names > Define. Name the range (no spaces) and click OK.

Then when you doubleclick the name in the Navigator, you'll go to that range.

Notes, Charts, Graphics, Drawing Objects, Etc.
Insert a note under Insert > Note.
Insert a chart under Insert > Chart.
Insert a graphic under Insert > Picture > From File.
Insert a drawing object by using the toolbar under View > Toolbars > Drawing.

Then the items you insert show up in the Navigator. Double-click any item listed to go to it.

---

---

PDF Reference to the New OpenOffice.org Chart Tool

I've done an overview/walkthrough of the new OpenOffice.org charting tool that will be released with version 2.3. It's a procedural approach, so to supplement it here's a PDF reference to some of the more complicated windows. I'll be including it and similar information in an update of my workbooks and Guidebook as soon as 2.3 has been released, and I'll provide the update to the book in PDF form, as well, so those of you who've already purchased the Guidebook can have the new chart info.

A note on the new chart tool -- in this window, you can specify multiple noncontiguous ranges. However, sometimes especially when they're in another sheet, they don't seem to take. Might just be a bug not worked out yet.

 

So for now, to more reliably add noncontiguous sections, just skip that window in the wizard, click next, and use this window. Click Add, then you'll get an item called Unnamed Series for which you can set the range of cells for the name (label/heading), the categories (must be the same as all other data series) and the Y values.

---

---

The new OpenOffice.org Charting Tool Coming in OpenOffice.org 2.3

For anyone who has grown weary of the current OpenOffice chart tool, or for anyone who has attended my classes and grown alarmed at hearing that charting is as much art as science, there is some relief in sight.

The chart tool has been improved significantly, and it's going to be included in OpenOffice.org 2.3, to be released roughly sometime this fall. If you're curious, you can download the developer version containing the chart tool here.

Read more
Download

Here's a comment from Pete who appears to be involved in the chart development.

Thanks for the nice overview. 
Please mentions also that many limitions will be overcome with the next
release. It's not only about a new (and shiny :) ) wizard. For example
multiple data series will be possible. Error bar for most kind of
charts will be possible. Regression lines with the function and R^2 are
implemented too. The performance regarding large data sets is improved.
EXCEL im- and export was improved considerably. Many of these features
are essential for engineers, students and scientists in their daily
job.
Worth to read:
http://graphics.openoffice.org/chart/whatsnewinchart2.html

However, there is still a long list of existing issues though OOo is
close to perfection. Any Volunteers will to finish the few remaining
items?
http://www.openoffice.org/issues/buglist.cgi?issue_type=DEFECT;issue_type=TASK;issue_type=PATCH;issue_status=UNCONFIRMED;issue_status=NEW;issue_status=STARTED;issue_status=REOPENED;email1=;emailtype1=exact;emailassigned_to1=1;email2=;emailtype2=exact;emailreporter2=1;issueidtype=include;issue_id=;changedin=;votes=;chfieldfrom=;chfieldto=Now;chfieldvalue=;short_desc=;short_desc_type=substring;long_desc=;long_desc_type=substring;issue_file_loc=;issue_file_loc_type=substring;status_whiteboard=;status_whiteboard_type=substring;keywords=;keywords_type=anytokens;field0-0-0=component;type0-0-0=equals;value0-0-0=Chart;field0-0-1=short_desc;type0-0-1=substring;value0-0-1=chart;field0-1-0=short_desc;Submit%20query=Submit%20query&order=issues.issue_id

Here's a tour of it. I'm getting to know the details myself right now, but this is the gist. OK, a fairly detailed gist.

Here's the basic data we're working with.

I like to select the data since it means less typing, or just less dragging, later. Select the data and choose Insert > Chart. You get this. Click this and any other illustrations to see them bigger.

Window 1: Chart Type
Note that you get a preview of the chart as you go along, in the document. This preview of course disappears when you click Cancel without creating a chart.

There are more up front variations, it seems. Here are a few.

Who doesn't love an exploded donut?

Or a nice XY Scatter.

Or a Net.

A Column and Line with options for how many lines.

And here are the 3D options.

In this window you just pick a type, a variation, any associated options, and you click Next.

Window 2: Data Range

Here you get the choices for what data is involved. Normally in a simple chart you could just have A1 to D5 in a contiguous block. But when you don't have that, you get to specify that ahead of time. Not that this is new but it's more obvious and easier.

You could change the range just by typing something different in the Data Range field. In this, I skip the D column entirely. Use a semicolon to separate non-contiguous ranges of data.

You also have the option, again not new, to have the data in rows or in columns.

Rows

Columns

And you also get the options at the bottom of the window, First Row as Label and First Column as Label. This is the same kind of thing you specify when sorting; you're saying, if you check the box, that the first row is identifying data, not data to be charted, and ditto with the first column. You usually want to mark both. Or at least I do.

Here's what the chart looks like with both boxes marked.

And the same chart with both unmarked.

Click Next.

Window 3: Data Series

Here, you get even more control over exactly what cells are used for the labels (Names) and for the data (Y-Values). For instance, if you wanted the label for Sales to not be Sales but to be some other term off in cell J14, you could enter $J$14 instead in the Range for Name field shown.

Select Names, then select each of the items in the Data Series list and make changes if necessary for them in the Range for Name and Categories fields.

Then select Y-Values, and do the same thing for each item in the Data Series list.

If you want to add an entirely new set of data to the chart, just click Add. You'll get an Unnamed Series. Then set the Name and YValues for that one, too. Or click Remove to remove any chunk of data in the Data Series window from the chart.

Note that the Categories will be the same for all data series. If you change them for one item in the Data Series list, they'll change for all.

When done, click Next.

Window 4: Chart Elements
You're nearly there. This is the simple stuff.

Then click Finish. And you've got your chart.

Modifying the Chart Once It's Created
Modifying after creation is a tish less twitchy though not that fundamentally different.

One thing: used to be, you could just select the chart by clicking on it once, right-click, and choose to change the data series. Now you just need to double-click it, then right-click and choose Data Ranges.

Essentially,  you double-click the chart, then either rightclick, use the Format menu, or use the Format toolbar. You can also double-click on an item in the chart.

The menu and toolbar have a fewer options, which will take getting used to but is a little simpler.

Here's the menu, and the toolbar after it.

To make a change to a particular bar in this chart, you do essentially the same thing as you used to. Double-click the chart, click on one of the bars to get the data series, then click on the specific bar to modify.

- Third click

- Fourth click

- Options under Object Properties (right-click, double-click, or use menu or toolbar)

- And results

To change titles, just double-click on them and type (after the requisite click, then double-click). This is the same as before.

Overall, not a makeover top to bottom. But nicer. I'm not a frequent chart user, so please give me your comments on what is the most useful.

---

---

Inserting Page Breaks, and Dragging the Page Breaks to Where You Want Them, in OpenOffice Calc

See also this post and this post on printing spreadsheets.

In Word, I believe, and possibly other applications, you can drag the default page breaks to where you want them, in print preview.

In OpenOffice.org Calc, there is no feature to do this in the page preview window.

But there sure as heck is one in the Page Break Preview window. You can insert page breaks, and drag default or inserted page breaks to where you want them.

Turning on Page Break Preview
Choose View > Page Break Preview. To turn it off, choose View > Normal.

Here's what it looks like.

Inserting a Manual Page Break
You can do this anytime, regardless of whether Page Break Preview is on. However, this illustration shows it on. Click on a row or column, and choose Insert > Manual Break. The appropriate selection will be available.

Dragging Page Breaks
Move your mouse over the page break and watch for the mouse pointer to turn into a two-ended arrow.

Then drag it right or left, up or down. The page break will change position accordingly.

If you drag an end or side page break into the document, then the column(s) or row(s) will appear as shown and they won't print. This is one way to control what prints, along with page ranges (select cells and choose Format > Print Ranges > Define for the first one in a document; Format > Print Ranges > Add for subsequent ones).

---

---

Printing Only the Sheets and Pages You Want, in OpenOffice.org Calc

this post and this post on printing spreadsheets.

You're going along, minding your own business, doing your spreadsheet. You print. Ten minutes later the irate printer guy comes along, saying "Would you mind not printing 200 pages at once????"

You're irritated since you just....well, you just MEANT to print the current sheet which has three pages.

But when you click Print or choose File > Print and don't modify this in any way, you print the WHOLE spreadsheet. Possibly including blank pages.

There are several ways to change this.

To print only the current sheet by default and prevent any blank pages from printing:

Choose Tools > Options > OpenOffice.org Calc > Print.

Select both options shown. Click OK. Now only the current sheet will print, and no blank pages will be printed.

To do this on the fly for each time you print.
Choose File > Print, click Options, then set the options in the options window that appears. This will make the change only for the current print job.

To select more than one sheet to print:
Click on the first sheet you want to print. Then hold down Ctrl and select any other sheet(s).

Or for contiguous sheets, click on the first sheet you want to print. Then hold down Shift and select the last sheet you want to print.

---

---

Right-click to add a note in OpenOffice.org Calc

I understand that in Excel, you have the option to right-click and add a note to a cell.

For a long time, OpenOffice.org didn't have that option. You could choose Insert > Note, or you could add that function to a keyboard shortcut to avoid using the mouse. However, many people were used to the right-click.

Well, it's here. In 2.2 and possibly earlier versions, you can right-click in a cell and get the note option.

A box will appear; type the note.  The display, while you're typing, is kind of twitchy. However, the whole thing does get entered correctly.

When you move your mouse over the cell (you don't have to select the cell), the note displays.

Right-click on the cell to choose to delete or make other choices re the note.

To print notes, choose Format > Page, Sheet tab, and in the Print section in the middle, select Notes.

The notes are printed after the relevant sheet, with a cell reference.

---

 

Creating Keyboard Shortcuts in OpenOffice, including Assigning Keyboard Shortcuts to Styles, a GREAT Feature

One of the complaints people have when switching from any software package to another is that the keyboard shortcuts that they're used to don't work anymore.

However, in OpenOffice.org you can set your keyboard shortcuts pretty much any way you want. You can even assign shortcut keys to styles. This means that:

• You can blow through formatting quickly without using the Styles and Formatting window
• You can set up styles, put them in the default template that your users use, then just give them all quick reference guides that might look like this:

         

        Text with hanging left indent   In WordPerfect was [whatever]  In OpenOffice Ctrl F4
        Heading indented from left and right   In WordPerfect was [whatever]  In OpenOffice Ctrl F6
        Back to normal text   In WordPerfect was [whatever]  In OpenOffice Ctrl Q

Assigning a Keyboard Shortcut to a Task

1. Create or open a document in the program where you want to apply the shortcut. You'll be able to choose that program, such as Writer, or all of OpenOffice.org, as the context in which the shortcut will work.

2. Choose Tools > Customize, Keyboard tab.

3. Select the program, such as Writer, or OpenOffice.org, at the top.

4. Use the Category and Function lists at the bottom to select the feature you want to assign a shortcut to. You have to be willing to spend some time looking but you'll eventually get a sense of where things are.

5. Find the keyboard shortcut, in the Shortcut Keys list in the top half of the window, that you want to assign. If it's already assigned to something, that's fine. Select the keyboard shortcut you want.

6. Click Modify. The shortcut will be assigned to the item.

7. If you want to remove a shortcut key from an item, select it in the Keys list and click Delete.

8. Click OK.

Assigning a Keyboard Shortcut to a Style

You might find it easier to just use a keyboard shortcut for styles, than to double-click them in the Styles and Formatting window. (Format > Styles and Formatting.)

To use a keyboard shortcut for a style, you do pretty much the same thing.

1. Create or open a document in the program where you want to apply the shortcut. You'll be able to choose that program, such as Writer, or all of OpenOffice.org, as the context in which the shortcut will work.

2. Choose Tools > Customize, Keyboard tab.

3. Select the program, such as Writer, or OpenOffice.org, at the top.

4. In the Category list, scroll to the bottom and select Styles. Expand the + next to it and select the category of style: Paragraph, Page, etc. Then in the Function list select the specific style. Select the shortcut you want from the Shortcut Keys list and click Modify.

5. Click OK.

Remember, the style has to be in the document where you use the shortcut key, otherwise of course it won't work.

Giving the Configurations You've Made to All Users

Shortcut keys are stored here in XP:

openofficedirectory\soffice.cfg\modules\swriter (or another module) \accelerator\en-us\default.xml

If you want everyone to have the same shortcuts, you can modify that file, then copy it to other machines or user directories. This is the directory on XP; different for Vista. (Grrr.....my impression of Vista is, OK, it's pretty because it looks like Mac, but I am sure sick of the blue screen of death.)

In Vista, it's

\Users\username\AppData\Roaming\OpenOffice.org2\user\config\soffice.cfg\
modules\swriter (or other module)\accelerator\en-US\current.xml

If you're having trouble finding the location, just make a change, then search your system for files that were recently modified, or contains a word in the change you made.

Here's what it looks like.

xlink:href=".uno:StyleApply?Style:string=HangingIndent&
FamilyName:string=ParagraphStyles" accel:shift="true" accel:mod1="true"/>

---

One Way to Export Data From OpenOffice Base to a Spreadsheet

In OpenOffice.org 2.2 you can now export data in a reasonable manner. Here's one way in 2.4.

Here's another way you might try. It's almost identical to the (albeit invisible) import method.

1. Open your database .odb file.
2. Click the Tables icon.
3. Right-click on the name of the table to export. Choose Copy.
4. Go to a spreadsheet. Paste.

The data will appear.

---

Repost to make some blogs searchable

Technorati has done it again -- I have a three week gap of unlisted posts. I've reposted them but that usually doesn't work, so I'm listing them here with links and some keywords.

OpenOffice Impress Master Pages: The Disappearing Act

Impress is....well, it's not always as solid as the other OpenOffice.org applications. However, there are ways to deal with it.

I'm going to post about two particular Impress issues: the disappearing master page, today, and on Friday, the wackiness surrounding creating two new master pages at once.

Master pages are the backgrounds for your presentations. A template or presentation might have one or more master pages in it.

(Read more)

OpenOffice Impress Issue: Applying Formatting to the Wrong Master Page

I posted about the disappearing master page Wednesday. Today I'll talk about the wackiness surrounding creating two new master pages at once.

Now, this isn't that big an issue. Basically, the rule is, just create one new master page at a time. Create it, apply it, save the presentation, maybe go get a cup of coffee or eat lunch. Then if you need to, go ahead and create another new master page from scratch in that same presentation or template.

Why?

Because when you've got two or more new master pages being created at once, some of the formatting, especially the bullets, of the second one you create will be applied to the first one.

(Read more)

Lots of Extra Settings for Firefox

This is another tip I've cribbed from Dave Richards, of the City of Largo. 

The reason that Firefox took off where Netscape floundered is that they had the guts to just take stuff out and make it what most people need: a nice little no-fuss browser that loads fast. (Bill Joy also has a good quote about how it takes real guts to cut features -- wonder what he thinks about Java now. )

So that's what Firefox is. However, there are many features, some very useful and some perhaps more obscure, like the number of threads to keep open for downloading graphics. If you want to get at them, just type about:config in a Firefox browser window.

(Read more)

Support ODF; Support Democracy in Texas!

Texas is discussing open document format, the format used by OpenOffice.org and not so much by Microsoft.

Read more on Sam Hiser's blog.

http://fussnotes.typepad.com/plexnex/2007/03/support_odf_sup.html

---

I posted about the disappearing master page Wednesday. Today I'll talk about the wackiness surrounding creating two new master pages at once.

Now, this isn't that big an issue. Basically, the rule is, just create one new master page at a time. Create it, apply it, save the presentation, maybe go get a cup of coffee or eat lunch. Then if you need to, go ahead and create another new master page from scratch in that same presentation or template.

Why?

Because when you've got two or more new master pages being created at once, some of the formatting, especially the bullets, of the second one you create will be applied to the first one.

(Read more)

Texas is discussing open document format, the format used by OpenOffice.org and not so much by Microsoft.

Read more on Sam Hiser's blog.

http://fussnotes.typepad.com/plexnex/2007/03/support_odf_sup.html

OpenOffice Impress Issue: Applying Formatting to the Wrong Master Page

I posted about the disappearing master page Wednesday. Today I'll talk about the wackiness surrounding creating two new master pages at once.

Now, this isn't that big an issue. Basically, the rule is, just create one new master page at a time. Create it, apply it, save the presentation, maybe go get a cup of coffee or eat lunch. Then if you need to, go ahead and create another new master page from scratch in that same presentation or template.

Why?

Because when you've got two or more new master pages being created at once, some of the formatting, especially the bullets, of the second one you create will be applied to the first one.

(Read more)

Support ODF; Support Democracy in Texas!

Texas is discussing open document format, the format used by OpenOffice.org and not so much by Microsoft.

Read more on Sam Hiser's blog.

http://fussnotes.typepad.com/plexnex/2007/03/support_odf_sup.html

Doing Equations, Formulas, Pi, Etc. in OpenOffice Writer and Calc -- And Impress, Updated February 2007

Updated February 2007 for Impress -- see end of post.

Sooner or later, no matter who you are, you're going to have to talk about pi. Or you're going to need to talk about squaring lambda. (Mmm....squaring lambda.....) Or you'll need to have a+b+c divided by 2.

How do you do that in OpenOffice?

The first step is to just go to the old reliable Insert menu. Anything out of the realm of plain text, just go to the Insert menu.

Just Using the Special Characters Window

Now, if you just want a Special Character, pi or lambda or something, you can choose Insert > Special Character.

Find the one you want. If you select several you'll see them all displayed at the right side of the window and they'll all be inserted.

Then just click Insert. The character will show up.

It's a pain to scroll through all that again and again so make an AutoText entry for it. See

http://openoffice.blogs.com/openoffice/2006/03/automatic_text_.html

Using the Formulas Features

If you need something more complex, then instead, choose Insert > Object > Formula.

You get an editing window at the bottom, a box for the equation in the document, and a little shortcut window floating off to the side.

Now,  you can use the little shorcut window. Click an item above the line, then click an item below the line and that inserts some placeholder stuff for you in the editing window.

   

But frankly I find it not that helpful since just writing the formulas is reasonably easy once you memorize a few tips.

• Use the Formula Reference Tables online help list to see how to enter formulas. Basically, do it how you think it would work. Use the OpenOffice.org Math Examples online  help list to see examples. These are really good. Just press F1 while you're in the editing window; you can type the titles of these topics into the Find or Index window.

• Use ^ for exponents, as in 3^2  which would be three, squared.
• Use sqrt for square root
• Use % in front of the written version of a symbol, as in %pi

So here are a few formulas. They're pretty easy to figure out. Click each image to see a slightly larger version, if you like.

a + b / $pi

a + b over $pi

(a + b) over $pi

If You Don't Know How to Write Out a Character Like %pi or %rho

Click the Sigma icon at the top to add a special character.

You can just scroll  through and select something from the list, and insert it.

Or you can add something yourself if you don't see what you need.

Optional: Add your own symbol   

To add something you don't see, click Edit.

Find what you want, by scrolling and manipulating all the dropdowns. Then name it, and click Add, not Edit.

The new symbol will show up in the symbol list.

Click in the document to stop editing the formula.

If you want to get back into the formula to change it, double-click the box the formula is in.

Formatting the Formula

All right. You've got a great formula. But it's really small. Or you'd like a different font. You change these by selecting the formula in the editing window and click on the Format menu.

Choosing Fonts gives you this window. You get to choose the font by the type: variables, etc. Click and hold down on the Modify button to change any font.

Select the font in this window, then click OK all the way back out of the windows.

That's About It

Insert > Object > Formula. Type what you want and use the online help and the brief tips I gave you.

Click in the document when you're done, and double-click the equation box to start editing again.

To format, select the text in the editing box and find the Format menu.

To add a symbol you don't know, click the Epsilon icon at the top of the window and select one--or click Edit to create your own.

Doing All This in Impress

I'm using 2.1 in February 2007 and it works just fine.

Click in a bulleted item and choose Insert > Object > Formula.

You can also paste the formula object from Writer to Impress. Don't paste it into a bullet in this case; just paste it into a layout with no bullets.

Double-click to edit, as usual.

Double-click the formula object to get into edit mode. Use the Format menu or make other changes.

---

Backspace, don't Delete, to delete cell contents in OpenOffice Calc Spreadsheets

Deleting in OpenOffice.org Calc's spreadsheet can be a bit annoying. You get this window each time.

It can be useful when you want very specific delete options. But sometimes you just want a plain delete with no backtalk.

How do you get that? Just use Backspace instead of Delete.

---

 

Very cool feature in OpenOffice Calc spreadsheets: Edit > Paste Special, Transpose

Chris from the Boulder LUG pointed this out to me last night while I was singing the praises of Edit > Paste Special in OpenOffice.org.

Let's say you've got this data.

It's accurate, but you want the columns and rows in different positions. You want it flopped 90 degrees like this.

Here's all you need to do.

1. Copy the original data.

2. Choose Edit > Paste Special.

3. Leave All selected and select Transpose.

4. Click OK.

And there you are, with the whole thing flopped around the way you want it.

Now just delete the old data, drag the pasted data over it -- whatever you want.

---

Conditional Formatting in OpenOffice Calc Spreadsheets

I saw this article in Lifehacker this morning.
http://lifehacker.com/software/excel/excel-tip--conditional-formatting-231340.php

All about how conditional formatting in Excel is so useful. Well, yes, it is, but (as I said while bristling slightly, in my comment on the lifehacker site ;>  )  you can do the same things in OpenOffice.org Calc spreadsheets.

Here's how you use conditional formatting.

First, you have to make styles. There's no way around this but it's very simple.

1. Choose Format > Styles and Formatting.

2. Be sure that Cell Styles are displayed in the Styles and Formatting window.

3. Right-click in a blank spot and choose New.

4. Name the style something appropriate.

5. Define the style.

6. Click OK.

Next, just set up the conditions. Let's say that you want to have a total show up with the GreenBold style you just created, if it's 500,000 or more, and you want it to show up as RedItalic style if it's 200,000 or less.   Click the following image to see it bigger.

---

OpenOffice.org Calc Project: Child Support Calculation Worksheet (Or Anything Else With Lots of Totals and Variables)

I was training some pretty advanced students this past week in Frankfort, Kentucky, and they do legal-oriented things, as well. So I put together an exercise where they created a child support calculation worksheet. Now, I know this is an emotional issue for a lot of people ;> but I want to emphasize that the features that I pulled together here can be used to calculate other things, as well. (You might also want to check out Scenarios.)

Here are  the features I combined.

Referencing a total in another sheet -- You can have one sheet to add up things like the components of the parents' income: W2 income, royalties, etc. You could have another sheet to add up things like expenses: rent, dog care, etc. Then copy the total cell, and choose Edit > Paste Special, Link, to take each of those totals to a third sheet and subtract expenses from income.

Whenever the components of the total in the first and second sheets are updated, then the totals in those sheets and in the third sheet will be updated.

Dropdown lists -- A common specification you'll need to make is who the custodial parent is. You can use a dropdown list under Tools > Validity to create a list that has two values, Mother and Father.

Choose Tools > Data Validity, and in the Criteria  tab, select List. Type the values you want, and don't allow empty values.

Then in the Error Alert tab, be sure the error checkbox is selected. Click OK.

Here's what the list looks like.

Using help under Data > Validity -- Sometimes it's hard to figure out what to enter.  In this case you can provide help. Choose Data > Validity, select the Input Help tab, choose to show input help, type what you want the user to read, and click OK.

The help then appears when the field is selected.

Using the IF function -- You create a condition, like IF B4 = "Mother".   Which means, if the custodial parent is the mother. You then create a statement for what you want displayed if that's true, and what you want displayed if that's false.

The following statement, in the field that displays what the father owes the mother per month, says that if the custodial parent is the mother and that if the father's net is more than the mother's, the father owes the mother the difference in their incomes, divided by 10, multiplied bythe number of children. Othewise, he owes her nothing. Obviously, the calculation for the amount owed should match the law rather than whatever I make up here on the fly.

=IF(B1="Mother"  AND  (C11>B11);(((C11-B11)/10)*B2);0)

Protecting cells -- If you're letting people download this spreadsheet to do their calculations, you don't want them changing any of the cells that contain calculations. You'd protect all the totals cells, the referenced cells, and the calculation for child support containing the IF statement, of course.

Select all the cells in a sheet and choose Format > Cells. In Cell Protection, turn OFF protection. (Bear with me.) Click OK.

Then just select the cells you want to protect (select - Ctrl to select multiple noncontiguous cells). Choose Format > Cells again, and turn ON cell protection. Finally, choose Tools > Protect Document > Sheet and enter a password to protect all protected cells in that sheet.

Here's the spreadsheet; it's just a hack so of course not legally binding but you can get the impression of the power of combining these features. The password is password if you'd like to take that off and play with the spreadsheet.

---

Mail Merge in OpenOffice Writer: Creating Mail Merge Documents From Text/CSV or Spreadsheets

I've got a lot of info out there, including lots of coverage in my book, about mail merges. However, I don't have a nice simple straightforward blog on it with everything in the same place all spelled out. Didn't, that is. This is all you need to do to make a nice simple document based on data in text files or spreadsheets.

What You Have to Do

1. Get your data. You've already got it, probably. This blog  is for people with data in text files, and in spreadsheets.

2. Turn it into a data source.

3. Create your mail merge document and suck the data in through the data source.

4. Print, specifying how many of the data records you want to print for, and whether to print to a file or printer.

1. Get Your Data

You probably already have it. It's in a .txt file or .csv that's comma or tab separated, perhaps. Or it's just a spreadsheet.

2. Make the Data Source: Text File Instructions

If your data is in text files, follow these steps.

1. Choose File > New > Database.

2. Make the selection shown, with Text as the format.

3. Click Next.

4. Specify the DIRECTORY where the text files are. Each text file in that directory will be a table in your database. Then select the item separating fields, i.e. a tab or comma or something else.

5. When all the settings look correct, click Next.

6. Umark the option to open the database for editing. You can open it; you just don't have to.

7. Click Next.

8. Save the data source (aka database) under a name that will help you remember what it is.

You're done.

2. Make the Data Source: Spreadsheet Instructions

If your data is in a spreadsheet, follow these steps.

1. Choose File > New > Database.

2. Make the selection shown, with Spreadsheet as the format.

 

3. Click Next.

4. Specify the spreadsheet file. Each SHEET in that spreadsheet will be a table in your database.

 

5. Click Next.

6. Umark the option to open the database for editing. You can open it; you just don't have to.

 

7. Click Next.

8. Save the data source (aka database) under a name that will help you remember what it is.
 

You're done.

3. Create Your Mail Merge Document and Suck the Data In From the Datasource

You can also use the simple or complex mail merge.

Simple:  http://openoffice.blogs.com/openoffice/2006/03/techtarget_arti_1.html

Complex: http://openoffice.blogs.com/openoffice/2006/02/techtarget_arti.html

But this is a nice way to do it too.

1. Create a new Writer document or open a document containing text that you want in the mail merge document.

2. Choose View > Data Sources. Everything you've created will be displayed. Click the + sign by the data source you want to use, then click + by Tables til you see the data you want to use.

3. Type any content you want and do any formatting. You can do this later too.

4. Click on the NAME OF THE FIELD, not the piece of data, that you want in the mail merge.

5. Drag it into the document and release. The field name will appear.

 

6. Add any other content and fields you want.

Save the document. You're ready to print.

4. Print the Mail Merge Document.

1. Choose File > Print.

2. You'll see this message. Click Yes. DON'T MARK THE CHECKBOX SAYING YOU DON'T WANT TO SEE THE MESSAGE AGAIN.

3. In the print window, specify the range of records, if you don't want them all, and specify to print to a printer, or to files.

4. Click OK.

5. In the print window, specify the printer and click Print.

---

Weird Calculation and Sorting Problems in OpenOffice Calc Spreadsheets and Writer Tables

This is a followup of my post yesterday about correct cell format.

Sometimes weird stuff happens in cells.

• Calculations don't work. They just sit there displaying the formula instead of running and displaying the results.

• Date formats don't work. You type in a perfectly good date and some bizarre number appears instead.
• Sorting doesn't work. Sorting numbers might change the order, but to an order you don't expect.

Why? Because the cells think that they are text. So it doesn't make sense to do calculations. You can't multiple bread * milk, so  you can't multiply two other things that Openoffice thinks are text.

This happens in Calc spreadsheets and Writer tables. To find out if the contents of your cells are deluded about their identity, select them, right-click, and choose Number Format.

If you see this, then the cells have the wrong format.

To correct this in Writer, just select the correct format and click OK.

To correct this in Calc:

• Close the number formatting window
• Select the problem cell
• Cut out the contents -- be sure to select and cut the series of characters in the cell, not the cell itself
• Reselect the entire cell, then press Delete
• In the window that appears, be sure that Format is selected. Click OK.

• With the cell still selected, right-click again and choose Format Cells. Select the correct format and click OK.

• Choose Edit > Paste Special
• Choose Unformatted Text and click OK

• Now the calculation should work correctly. And if it wasn't a calculation but a number, now rerun the sort and it should work correctly.

See yesterday's post about number recognition, toward the end, for how to recognize a badly formatted cell that thinks it's text, even though it might have a $ and decimal places.

---

A Little Thing About OpenOffice Number Formats in Writer Tables and Calc Spreadsheets

I was just putting together a proposal, putting in the numbers for each category in a Writer table, just like I always do. I was applying automatic number formatting for the currency amounts.

The number formatting was being really annoying, though. I set the number format for a bunch of blank cells, then had to set it again when I put in the numbers since it didn't take.

I also had to re-apply currency formatting when I changed a number. It was $800.00, formatted correctly, and when I changed it to 900, it left-aligned itself and changed its number formatting to Text. That was particularly annoying.

Before we continue,  why do you care about number formatting? It's convenient but there's more. Note that the particular issue I encountered exists in Writer, but the problems if you don't have correct number formatting exist in both  Writer and Calc.

• It's easier to apply formatting that will make decimals and currency symbols appear than to retype it.
• Sorting won't work correctly if the numbers don't know they're numbers.
• Calculations won't work correctly if the numbers don't know they're numbers.

Back to the topic at hand -- why was this happening? Why was number formatting misbehaving? It usually doesn't. But it was today. It's because the default setting "Number Recognition" had been removed. Or maybe it's not on by default in 2.1.

Making Sure Your Number Formatting Sticks
Here's what to do to prevent number formatting annoyance.

1. Create your table.

2. Select the cells that you want to have numbers in.

3. Right-click and choose Number Recognition. You want a checkmark to appear next to  This will keep OpenOffice.org aware that the content you enter is, indeed, numbers and that the program should darned well apply number formatting when you tell it to.

4. Now, apply the appropriate number formatting. Right-click again on the selected cells and choose Number Format. Have your way with it, select the options you want, then click OK.

Notes on How to Detect Incorrectly Formatted Cells
A correctly formatted cell is right aligned like these.

You can have the currency symbol and decimals applied correctly, BUT if the numbers are left aligned, then they still don't know that they're numbers. They think they're text. Weird, huh? So keep an eye out for left-aligned correctly formatted numbers.

---

Things That Are Hard to Figure Out in OpenOffice Writer: Page Numbers, Different Page Orientations, Watermarks, New Document Formats, and Dragging Cells (Repost)

Another "classic" post! These are perennial questions and through reposting I'll be able to get these to come up as searchable in blog searches.

Note: Here's a related article I wrote for TechTarget.com.

I get a lot of questions when I train, and just in emails. A lot of them are about things that aren't actually hard to do but they're hard to figure out how to do in the first place. Here's how to do some of them.

Adding a Page Number to an OpenOffice.org Writer document
Note: See a related post on starting a document with no page number on the first page and page 1 on the second page.

You can do this a number of ways, but this is the quickest.

1. First, make a footer for the page number to appear in. Choose Insert > Footer > Default.
2. Scroll to the bottom of the document; you'll see the footer.
3. Click in the footer. Press Tab if you want the page number in the middle, press again if you want it at the right.
4. Type the word page if you want, followed by a space.
5. Choose Insert > Fields > Page Number.
6. Format the text in the footer the way you want it.

If you want to have no page number on the first page and start with 1 or 2 on the second page, that's a bit more advanced. Stay tuned for the blog on page styles.

(You can do the footer turn-on by choose Format > Page > Footers, too.)

Putting a Portrait Page and a Landscape Page in the Same Document

You absolutely can do this. It just takes a little while. You set up a page style that's horizontal, and one that's vertical, and then you just switch.

Here's a 2.0 document (twopagestyles.odt) that has a vertical page style and a horizontal page style. (You can set them up yourself using styles—Format > Styles and Formatting, then use the help.)

1. Type your content.
2. Choose Format > Styles and Formatting.
3. Click the Page Styles icon at the top of the Styles and Formatting window.
4. Double-click the first page style you want to use, the vertical or the horizontal. (It should probably be vertical, since this page style will apply to everything above your cursor in the document.)
5. Click at the bottom of the page using that page style.
6. Choose Insert > Manual Break.
7. In the Page Style list, select the other page style, such as Horizontal.
8. Click OK.
9. Put the content on that page that you want.
10. Click at the bottom of that page.
11. Choose Insert > Manual Break.
12. In the Page Style list, select the first page style, such as Vertical.
13. Click OK. Now you're back to where you started.

Stay tuned for the blog on page styles, to create page styles yourself from scratch, and to do some gnarly pagination control.

Getting exactly what you want when you choose File > New ____ Document.

The default empty blank text document and spreadsheet are fine but you'd like the margins to be wider, or the font to be different, or for it to have certain styles. It's easy to switch out what comes up under File > New > ____ Document.

1. First, make a new document or spreadsheet. Set up the page the way you want, create or import styles, add footers and page numbers, etc.
2. Then choose File > Templates > Save.
3. Name the document, leave the category My Templates selected, and click OK.
4. Choose File > Templates > Organize.
5. Double-click the My Templates category.
6. Right-click on your template you created previously and choose Set as Default Template.
7. Click OK.

Now that document will come up when you choose File > New > _____ Document (text or spreadsheet).

To go back to the original, just repeat those steps but instead of choosing Set as Default Template, choose Reset Default Template > Text Document or Reset Default Template > Spreadsheet.

Making a Watermark

If you want a graphic or piece of text behind the content of your page, you can approach it a few different ways.

If you want a text-based watermark for your document, like CONFIDENTIAL, behind just a few pages, follow these steps. You'll  need to place the text box on every page where you want it to appear.

1. Click the “T” text icon, or if you don't see one, choose View > Toolbars > Drawing to make it appear.

2. Draw a box with the tool and type what you want inside, like CONFIDENTIAL.

3. Select the text and make it really big, maybe 66 points. You can use the font size dropdown list on the object bar for this.

4. Make the text gray if you want it lighter. Use the Font Color icon on the object bar.

5. Click somewhere else in your document, like a blank spot or some regular text.

6. If you want the text vertical or diagonal, click on the text box, right-click and choose Position and Size, click the Rotation tab, and in the Rotation field type the number of degrees. (You can also click on a point in the Default Settings region.) 55 degrees is good for a diagonal watermark.

7. Click on the text box you just drew, right-click, and choose Wrap > In Background.

8. Drag the text box to reposition it if it's not where you want, make the text larger or smaller, rotate it more or less, and make any other adjustments.

Here's an OpenOffice.org 2.0 document (watermarktemplate.odt  ) you can use with a watermark.

If you want a graphic-based watermark for just a few pages of your document, paste it into your document on each page where you want the graphic, or choose Insert  > Picture > From File. Then  do step 7 from the previous step to wrap the graphic in the background.

To make the graphic lighter, select it. The Picture toolbar should appear but if it doesn't, choose View > Toolbars > Picture. Use the Brightness icon to make the graphic lighter and use the Contrast icon to decrease contrast.

To put a graphic in the background of every page of your document (every page with the Default page style, that is, or every page with the page style you modify if you know styles), follow these steps.

1. Be sure you have the graphic, that it's light enough, and that you know where the graphic is.
2. Choose Format > Page.
3. Click the Background tab.
4. From the As dropdown list, select graphic.
5. In the Type area, be sure Position is selected.
6. Click Browse and find the graphic.
7. Click Open.

Dragging a Cell in a Spreadsheet

I get a kick out of how obscure this is. You can select two or more cells and drag them, but you can't drag just one.

Unless you do this.

1. Select the cell.
2. Click and hold down, drag the mouse down one cell, then back up one cell, and release.

Now you can drag the cell wherever you want.

---

OpenOffice Templates: Making Them, and Making New Documents Based on Them (Writer, Calc and Impress): Reposted January 2007

This is embarrassing. I have a huge blogapalooza on styles but left out templates.

I shall remedy that immediately.

All right. Let's say you have the following:

• A bunch of styles you need to have when you create a document.
• Some canned text, like your signature or a graphic logo, that you use in a lot of documents
• Or you're just darned picky about how you want your documents ;>

You are an excellent candidate for using templates. Templates are documents that store styles, text, graphics, general formatting—whatever you want. You create a document, save it as a template in the Template Place, then point to that template when you want to create a new document using what it's got. (“Styles and canned text...that's what it's got....remember that.”)

And it really couldn't be easier.

Making a Template

Open a new document.

Put in it what you want in the template. Styles, graphics, text of every kind and variety. Whatever.

Then choose File > Templates > Save.

Specify My Templates unless you have another idea for a category.

(To create a new category, click the Organizer button, select a category in the button that appears, right-click on it and choose New. An untitled category will appear. IMMEDIATELY start typing the name of your new category. Click somewhere else in the category list when you're done typing. Click Close.)

Type the name of your template like Meeting Minutes.

Click OK.

Now you've got a template.

Using a Template

You need to create a new document using that template.

Choose File > New > Templates and Documents.

Select the Template icon on the left.

Open the category you chose like My Templates. You'll see your template.

Click Open and you'll have a new untitled document based on that template.

The Coolest Part—Making That Template Come Up When You Create Any New Document

Choosing File > New > Templates and Documents is fine but what if that's your template you use all the time? You can make it come up when you just choose File > New > [type of document] by setting it as the default template.

Choose File > Templates > Organize.

Open the category your template is in, in the left side.

Right-click on the template and choose Set as Default Template.

Click Close. You're done! Choose File > New > [type of document] and you'll see the effect.

To switch back to the normal original boring blank document, repeat the steps but this time choose Reset Default Template > [type of document]

Pointing to an Existing Directory of Templates

If you have a bunch of templates, Microsoft or OpenOffice.org, set up OpenOffice.org to know where they are. Then you can get to them as above by choosing File > New > Templates and Documents.

Converting, If You Want to Point to Microsoft Format Templates
First, if they're in Microsoft 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.

Pointing to Templates to Use
Now, choose Tools > Options > OpenOffice.org > Paths. Select the Templates line.

Click Edit, then click Add, and add the path to the templates you want to point to. (Click this image to see a larger version if you like.)

Click OK, and OK, and OK  again.

Now you'll see the templates when you choose File > New > Templates and Documents.

---

Interesting New Calc Logic in OpenOffice.org 2.1

This might have been there previously, but I just noticed it now.

You know the Quick Sum feature. Click at the bottom of or to the right of a column of numbers, click the icon, press Enter or the green check mark, and you get the sum.

Check out how smart it is, though. When you've got some subtotals in the column, it just adds up those numbers.

Now, it only works this way when you have the lines as shown with no empty rows. If you have empty rows, the sum just includes the first subtotal, or the first field period, and you have to update the range to get what you want.

If you do subtotals a lot, check out Data > Subtotals.

---

Getting an HTML Table Into a Spreadsheet in OpenOffice Calc

I blogged here about how to get a spreadsheet into a Writer table.

As for the opposite, getting a Writer table into a spreadsheet, you just copy and paste. Very easy.

How do you get an HTML table into a spreadsheet?

Or, frankly, how do you just suck some HTML content into a spreadsheet?

You should be able to copy and paste--when I paste a table, it comes into the spreadsheet nicely with the rows and columns  retained correctly. However, I understand that doesn't work for everyone. So you can use this approach, instead.

Also, if you are so inclined, how do you get it to update regularly? Maybe you want to display stock quotes from a Web page--you can choose to put those in a spreadsheet and have them update every 30 or 300 seconds.  (This feature works correctly with links to files and it really should work, in theory, to link your spreadsheet to a web page on the net but I can't get it to work with a URL, just with a file. If anyone else can, write and let me know how.)

In this case, you might always want to use this approach.

1. Go to the web page containing the table that you want to use. Save the file to your computerif it's not there already. The window and the online help indicate that you can just link to a URL starting with HTTP but I can't make it work.

Also, take a look at the tables if there are more than one. You're going to need to figure out which table you want by knowing what order the tables appear in.

2. Go to your spreadsheet. Click in the cell at the upper left range of where you want the table(s) to appear.

3. Choose Insert > Link to External Date.

4. In the window that appears, browse to the file that you saved to your computer. If you want to try making an HTTP URL work, then paste in that URL instead. The online help says to paste it in, then press Enter.

5. Select the content you want to insert. HTML_All inserts everything in the HTML page. HTML_Tables inserts all the tables. HTML_1 inserts the first table, and so on.

6. Select the Update Every option, if you want to update the content, and specify how often in seconds. This does work, but it has a drawback that we'll see later.

7. Click OK.

8. The HTML content you selected will appear.

9. To format the content to wrap within the cell, select the cells, choose Format > Cells, and in the Alignment tab select the Wrap option shown. Click OK.

10. Whenever the HTML file changes, the spreadsheet updates. However, as you will see, all your beautiful wrap formatting goes away.

If your content in the table is fairly wide, then:
- Resign yourself to it looking like this in the spreadsheet
- Widen the cells in the spreadsheet
- Don't choose to update the content

 

---

Using Master Documents to Combine Spreadsheets as Well as Writer Documents

I've written an article for TechTarget.com about how to bring spreadsheets into master documents, too. You link the spreadsheet to a Writer document, then bring that "dummy" writer document into your master document. It's a hack but a practical hack since big publications often need to combine different document types. There's an approach (even hackier, but useful) for bringing in presentation content, too.

If you haven't read the first article on plain old master documents, read that first.

 

---

TechTarget Article: Getting Data Back Out of OpenOffice.org Base Databases

I've written an article for TechTarget.com on how to export data from a Base database to a spreadsheet or text file.

http://searchopensource.techtarget.com/tip/0,289483,sid39_gci1226457,00.html

See this blog for how to import data from a spreadsheet into a Base database. (This creates a true Base database, as opposed to a database file that points to a spreadsheet.)

---------
Vote for adding a wizard to import data into Base
http://qa.openoffice.org/issues/show_bug.cgi?id=51904

Utility to export CSV from Base
http://wiki.services.openoffice.org/wiki/CSV_export
---------

Exporting data from an OpenOffice.org Base database

Solveig Haugland
10.30.2006

Digg This!     StumbleUpon     Del.icio.us

I think somebody on the OpenOffice.org database development team has a mystery fetish. He or she read too many murder mysteries or hardboiled detective novels early in life or worked for too many years as the White House Easter Egg hunt director. Whatever the reason, this apparent fetish shows up in the Base module.

The best mail merge tool isn't on any of the menus...you have to hunt it down and add it. You can easily get data into Base...but you won't find it by looking for a File > Import command. And if you want to get data out of Base....well, it's possible, but certainly not under anything as logical as File > Export.

This keeps me busy writing articles to bring all the mysteries into the clear light of day. This article is all about how to actually export data from your Base database.

Why export data in the first place?

You don't necessarily need to export data from Base. If all you do is enter data and pop it out in OpenOffice.org reports, you're fine. But let's say you need to submit your data to, oh, the IRS or another government agency. Or suppose you work with another organization that uses a different database. In this case, you'll need to provide your data to that group in a form they can read. That format is usually a spreadsheet or a delimited text file. This article will walk you through how to do it.

The technique is a hack, of course. (The way it's supposed to work....doesn't work.) But as hacks go, it's not horrible. You take the data from your table, view or query; bring it into a Writer document; then massage it to become a spreadsheet or delimited text file.

Step 1: Prepare the data

If you want to export all the data in a table, you can skip this. But if you need to export only certain records or certain fields, you need a query or view to restrict the data appropriately. I'm not going into detail about that here; see this article to create one.

Once you've got a query or view, complete this step to update and make sure your query or view contains the latest data.

1. Open the database file (File > Open and select the .odb file).

2. Click the Tables icon at the left to view views; click the Queries icon at the left to view queries.

3. Double-click the view or query.

4. When it opens, click the Refresh button.

5. Close the view or query and database, saving changes.

Step 2: Bring the data into a table in a text document

1. Create a new Writer text document (File > New > Text Document).

2. Choose View > Data Sources.

3. Expand the database and view the table, view, or query to get the data from.

4. Click the blank square in the upper left corner to select all.

5. Click and hold down on the blank square; keep your mouse held down.

6. Drag your mouse into the text document and release your mouse.

7. The Insert Database Columns window will appear.

8. In the window, leave Table selected, and click the >> button to move all fields to the right side of the window.

9. Click OK.

A table will appear containing all the data.

Step 3: Creating a spreadsheet or delimited text file from the data

We're on the home stretch now. If you want the data in a Writer table, you're done. But if you want it in a spreadsheet or a text file, follow these steps.

1. Select the entire table, including the headings.

2. Copy.

3. Create a new spreadsheet (File > New > Spreadsheet).

4. Click in the upper left corner.

5. Paste. The data will appear.

6. Resize the column widths as necessary.

7. Save the spreadsheet.

8. If you need to create a delimited text file, choose File > Save As and select Text CSV as the file format. Specify the name you want for the text file and click Save.

How it's supposed to work (but doesn't)

I'm including this because this might work sometime soon, and because the online help tells you to do it this way. I would still, of course, prefer that the next rev of Base include something simpler, like a File > Export function.

1. Open up your database file, then open your table.

2. Click the upper left blank square to select all, or Shift+Click to select certain rows.

3. Copy.

4. Paste into a spreadsheet.

But, as I said, this doesn't currently work in 2.03 or previous versions. (I haven't tested it on 2.0.4 yet, since it crashes each time I start it on both my computers.)

Another mystery revealed

Digging the hidden treasures out of OpenOffice.org is actually kind of fun. I enjoy donning my virtual trenchcoat and dark glasses. (I'm already planning who will play me in the movie, tentatively titled All Suarez-Potts Programmers.) Of course, it's not that fun for frustrated users; I hope that the annoyance and frustration among users is at least somewhat offset by my revealing the mysteries of the Base module here.

---

TechTarget Article: Pivot Tables/Data Pilot in OpenOffice Calc Spreadsheets

I've written an article for TechTarget.com on how to use the OpenOffice.org Data Pilot, aka pivot tables. There are some newish 2.x features so if you haven't looked at them recently, take a look.

http://searchopensource.techtarget.com/tip/0,289483,sid39_gci1228426,00.html

 

---

Creating Automatic Backups and Specifying Where They're Located in OpenOffice

Always have a backup. That's up there with wear your seatbelt, eat your vegetables, and always have cab fare on a first date.

OpenOffice.org has a backup system that lets you create a backup copy of your documents, anywhere you want. Not a bad idea, eh? Here's how you do it.

1. Choose Tools > Options > Load/Save > General.

Mark the checkbox for always making a backup.

2. In the same big window, open the OpenOffice.org category. Click the Paths option and select the Backups item. This is where the backups will be created. If that's fine, leave it as is.

If you want the backups elsewhere, click Edit, specify the new location, and click OK.

3. Click OK to save all the changes you made in the settings window.

For every new document you create, then each time you edit that document, a backup of the previous version will be created. Your backups will be created with a BAK extension in the location you specify.

 

---

Thanksgiving Calculator for Calc (and Excel)

If you're feeling a little freaked out about your Thanksgiving gathering, calm your nerves with the planning spreadsheet from Make magazine.

Calc Tricks: Frequency Tables and In-Cell Bar Graphics in OpenOffice Calc Spreadsheets

I don't know exactly what frequency tables are, even after my stats class circa 1988, But I know that if you need them, you need them. Here's a nicely explained and illustrated blog about using them in OpenOffice.org Calc, from Ernest.

Here's another link that Antonios tipped me off about. They're for Excel but I understand they also work in Calc.

Lightweight data exploration in Excel     "We often are given a chunk of data in Excel that we need to explore. Of course, the first tool you should pull out of your toolbox in cases like this is the trusty PivotTable (it slices, it dices!). But at times we have to dig a little deeper into the toolbox and pull out the in-cell bar chart. "

More on Excel in-cell graphing   "We received an enthusiastic response to our post on in-cell bar graphs in Excel. The community quickly explored every edge case. I want to highlight some of the great ideas raised. Henk was first out of the gate with a great suggestion that two columns could be used to show positive and negative values. "

 

---