OpenOffice.org Training, Tips, and Ideas

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

500,009 views!

I've been keeping an eye on my blog odometer and wanted to mention that just a couple minutes ago my blog hit half a million total views.  Small potatoes for the likes of, say, Lifehacker.com, but it's a milestone nonetheless. Thanks to everyone who comes by to look, and to everyone who links.

Solveig

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.

---

---

Back to School: A few of the many, many sites with free clip art for schools

I've been working on finding more fun goodies for education. Here are a few site with free clip art including stuff from the Discovery channel!

(I'm pretty sure that these are all free but I would be remiss if I didn't say that it's best to check the licenses yourself, as well.)

Some clip art

http://clip-art.kaboose.com/index.html
http://school.discovery.com/clipart/new.html
http://www.teacherfiles.com/clip_art.htm  (BIG collection)

Free animations
http://register.freeze.com/download/index.aspx?sx=348413c5-d957-4f6c-bfe7-d91ca643ee6a

Add it to the Gallery for easier access.

---

---

Very nice features in the gallery for putting graphics in the background on OpenOffice Writer

I learned this while training the fine folks at the Bainbridge Island School District. And man, it just goes to show that I should listen more to my own advice, which is:

When in doubt, right-click.

Here's the Gallery. It's a wonderfully customizable convenient area for storing images or video. (OR VIDEO.) There's not much in it but you can add whatever you like, using whatever legal clipart you can find anywhere. One excellent site is  http://www.teacherfiles.com/clip_art.htm

(Click any of these graphics to see them bigger.)

Click the Gallery icon or choose Tools > Gallery to add or show. It looks like a little framed picture, five from the left below.

Here are directions for using the gallery, including adding to it.

To add a graphic from the gallery to your document, just drag it in.

Now, a seemingly different topic. It's ever so slightly complicated, not really but not wildly obvious, to add graphics to the background of a document.

To put just one graphic in the background of whatever text it's by, you right-click on it and choose Wrap > In Background.

Now it's in the background.

To fade it, you click on the graphic, and in the Picture toolbar that appears, type 50% or more in the Transparency field and press Enter.

In another approach, to put a graphic in the background of the paragraph, or in the background of the entire document or at least all pages in the document with the current page style applied, you choose Format > Page or Format > Paragraph,  click Background, select Graphic, browse for the graphic, specify Tile Position or Size, and click OK.

To add it to the background of the header or footer, just click the Header or Footer tab of that same page style window, and click More.

Hey. There's an easier way, with the gallery. THERE IS A CATCH WHICH I EXPLAIN AT THE END OF THIS BLOG. NOT A BIG CATCH FOR MOST PEOPLE, BUT THERE IS A CATCH.

Show the gallery. Choose Tools > Gallery or click the little gallery icon.

Find the theme you want, the category at the left.

Find the picture you want.

Right-click on the graphic and choose Insert > Background> Page, Background or Header/Footer.  NOTE THAT THE DEFAULT FOR THIS IS FOR THE GRAPHIC TO APPEAR REPEATEDLY TO FILL THE AREA YOU SPECIFY.

To get the Header or Footer option, you need to have clicked in the header or the footer.

(To turn on a header or footer, choose Insert > Footer > Default or Insert > Header > Default.)

And there you are. It appears as it should based on your choice.

Background of page

Background of paragraph (sometimes it doesn't fit that well, so the first approach I mentioned earlier is better.)

Background of a header or footer. Note that if the graphic is big as shown the graphic won't all show at first.

So just click in the header or footer and press Enter to add space til the graphic shows sufficiently.

Undoing

If you want to undo it, then either press Ctrl Z or click Undo as many times as necessary, or

1. Choose Format > Page or Format > Paragraph, and click the Background tab.

2. Select Color instead of Graphic in the list at the top.

3. Select No Fill and click OK.

Issues
Now, the only thing is that:

- You can't fade it using this approach; you need to make the graphic file itself lighter.

- It repeats, it tiles, by default. To modify it:

     Choose Format > Page, Background tab and choose Position or Area instead. Position just puts it in one place, Area changes the size and aspect ratio of the graphic to fill the area.

    Choose Format > Page, Header tab or Footer tab, then click More and ditto above.

   Choose Format > Paragraph, Background tab, and ditto above.

Pretty dandy, eh?

ONE BIG FAT WARNING ABOUT THIS.

This does not STAY in the document if you save it in .doc format. You MUST save it in .odt format. Otherwise it will go away.

If you add graphics to the background of the header, footer, paragraph, or page, you have the exact same problem. Somehow Word format doesn't like background graphics.

---

---

Darkening the OpenOffice Impress or Draw Grid, and Changing the Increments

I was going to blog about this straight-up, but then I thought, hey, I've got this already in my book. So I've posted an excerpt from the book, a three-page PDF of the instructions for using the grid, plus darkening the grid and increasing the number of increments. 

The instructions are for Draw but apply to Impress as well; when you choose Tools > Options as in the instructions, just choose either Draw, or Impress, whichever application you want to modify the grid for.

Download grid.pdf

---

---

Podcasts?

I'm exploring a few ideas for multimedia, including video recordings and audio-0nly podcasts. Are there any audio-only podcast ideas you'd like to suggest, or do you think pretty much just the video would be best?

I've inserted a sample file just to get started as I work my way through learning the podcast process on Typepad.com.  There are a few squeaks in the audio; something else to work on.     Download pocastintro2.mp3  

I've also just recorded a three-part test podcast of how to create a simple mail merge, so I'll be posting that soon as well.

Thanks,
Solveig

Addon tools for OpenOffice.org Writer

I received this information a while ago and have been meaning to blog about it for some time.

http://nothickmanuals.info/doku.php/writertools

After a cursory look, it's a set of macros that provide the following tools:

• Lookup Tool allows you to lookup the currently selected word in several online references, including Cambridge Dictionaries, WordNet, and Google Define.
• Google Translate allows users to quickly translate selected text fragments between different languages using the Google Translate service.
• Show on the Map tool allows you to select a city, a street name, or a postal code and map it using the Multimap service.
• Email Backup can be used to quickly send a backup copy of the currently opened document to a specified email address.
• Multi-format Backup macro saves the currently opened Writer document in the Word, RTF, and TXT formats. The backup files are stored in a separate folder with a date stamp. On Linux, the macro also archives the resulting folder using the tar utility.
• Remote Backup allows you to quickly save a backup copy of the current document on a FTP server.
• Convert to DokuWiki converts the current document into DokuWiki format.
• Start/Stop Timer tool can be used to keep tabs on time spent on the currently opened document and save the time data (the document name, used time, and date) in the accompanying WriterDB database.
• Word Hunt Game is a simple game, where you have to guess the word randomly picked by Writer.
• Word of the Day tool picks and displays a random word and its definition from the from the accompanying WriterDB database. As a writer, you may often come across a new word or an interesting expression. The WriterDB database allows you to store your language findings in one place, while the Word of the Day tool helps you to keep them active in your memory.
• Add to Basket tool copies the currently selected text fragment into the basket table in the WriterDB database. It’s perfect for storing notes and text snippets.

The web site states that the Writer’s Tools extension is released under GNU GPL and that Writer’s Tools is very much a work in progress.

Once installed, you get this menu.

The lookup tool, for instance, gives you this window at first:

You then get this window or a similar one with the results.

Converting to docuwiki looks like this:

I got an error trying to use the add to basket feature. I might or might not have fully set up the database correctly.

Here's the first window of the translation feature:

And the results.
Places to Stay and Things to Do was translated into Plätze zum zu bleiben und Sachen zum zu tun by Google.

---

---

Review of Edubuntu from the School Library Journal

If you'd like to learn about Edubuntu, check out this review.

Printing Envelopes in OpenOffice.org Writer (Repost)

I ran into Christian Einfeldt at Linuxworld, who mentioned that he had some questions about printing  envelopes. It's a good perennial topic, so I'm rereposting.

(Originally posted December 2, 2005)

I get a lot of questions about envelopes. A lot. I wrote an article for TechTarget.com about how to do envelopes in OpenOffice.org 2.0.

I included some templates there; here are the same envelope templates for OpenOffice.org 2.0.

• Envelope template, not hooked up to a data source, using frames
• Envelope template, not hooked up to a data source, not using frames

The article is long and detailed. Here are the key points.

• You need to get to know your printer and let it know to expect envelope shapes, not letter or A4 shapes.

• You also need to fiddle around for a while and figure out where--left, right, perpendicular, parallel--in the tray your printer expects envelopes. Buy a box of cheap envelopes and expect to waste a few while you experiment.

• Then make sure that your envelope document is set up to print to the envelope size your printer is expecting. Envelope 10 is good.

• You can type in your data for the envelope or use a data source. My article goes over both.
• The simplest way is to just choose File > New > Envelope, then Format > Page Size and instead of Letter or A4, choose Envelope 10 and change the orientation to landscape.  Then either with standard margin formatting or with frames, put the text where you want.
• As another way to create the envelope document, you can also choose Insert > Envelope, and use the three-tabbed window that appears.
• Print the envelopes separately from your letter or other document that goes with the envelope. It complicates things too much with the paper size that the printer expects.

Then just print the envelope. (If you're using data sources, click Yes in the dialog box that appears asking if you want to print a form letter.)

The next time you want envelopes, just use that same document you already created, and change the addresses. Either save the document in myimportantdocuments\envelopes, or if you're a template kind of person, make it a template. (File > Templates > Save, select a category and name the template, then choose File > New > Templates and Documents and pick your envelope template.

Note: Doing it in 2.0 is quite similar to how to do it in OpenOffice.org 1.1. Here are my posts how to do that; they're excerpted from my OpenOffice.org workbooks.

• OpenOffice.org 1.x Mail Merges, Envelopes, and Labels
• OpenOffice.org 1.x Plain Labels and Envelopes

Tip on Printer Setup
Some of the pain of envelopes is the printer setup. Here's a GREAT tip from Miriam:
"I just read your envelope printing tip. Instead of constantly changing and checking the printer settings, I add another instance of the printer, configure it for envelopes and name it "envelope." When I want to print an envelope I choose this printer instead of the default one. That way my settings are always the same."

Envelope Mantra
Here's the other main point I want to make sure everyone understands.

Envelopes aren't too bad once you figure out how to do it the first time. Honest.

---

---

A Huge Printable PDF Article on Advanced Report Techniques, Including SQL-Based Calculated Fields on Reports

I realized that while this was published on TechTarget, I hadn't made much of a big deal of it here.

Here it is. Lots of fun, techy stuff about reports. Here's the TOC.

A Quick Review of the Report Writer Tool

What You Can Do in the Query Design Tool and SQL View

What You Can Do With Report-Specific Fields

Quick-and-Dirty Reports

Creating Your Own Reports Using the Next Record Field

Powerful Reports Can Be Fun....If You Enjoy Getting Your Hands Dirty

 

Here's the link. It's a big PDF.

---

---

OpenOffice.org Certification

Often I like to ask for opinions to help form what I think. But not this time. ;>  Here's the deal. We need OpenOffice.org certification. In a big way.

I'm at LinuxWorld. There are Linux certifications. Tests are going on as I blog. Training and study guides are offered. But nowhere is there any OpenOffice.org certification.

Why not?

This isn't the first time I've thought about OpenOffice.org certification, of course. In 2001 I called Sun and talked to them about StarOffice certification. The person I talked to claimed that certification wasn't profitable. I don't have actual figures on the Sun Java certification, but I KNOW that it's profitable. ;> And Microsoft does office suite certification. Microsoft does many things one might or might not want to emulate but you really can't accuse them of doing things that aren't profitable, one way or the other.

So back to LinuxWorld and OpenOffice.org certification, 2007. I talked to some folks from LPI, who are huge in Linux certification. I asked if there was any interest in OpenOffice.org or desktop certification, and they said they were definitely intersted.

There are books all over the place, study guides, for Linux certification. Not to mention the millions (I assume) of people and dollars being made from Microsoft certifications. Java certification is huge; check out www.javaranch.com and the many many many other resources.

 

I'm not sure how many technical products there are that you *can't* get certification for, in fact.

People need to be able to be certified, at at least two levels, on OpenOffice.org. That means certification objectives, an exam with well written challenging questions, places where you can take the exam, and of course a mix of free and paid study guides and training resources for preparing for the exam. Probably a few other things that I'm leaving out.

I forget where I read the quote, but the gist was "If you measure it and reward it, people will do it." There's a certain internal benefit to being *certified* in something rather than just knowing about it.

Here are some additional reasons.

• Many organizations have a free-floating training budget, and people get to take training every year. Would you rather take training with a certification as the end result, or just training to know about stuff? As a manager, would you put something on the training list that results in certification, or just something to provide capabitilies? If there's an OpenOffice.org certification, then it's more visible, it gets on more lists, and more people take the training.
• Community and vocational colleges are typically focused on getting their students out the door with practical, immediately valuable skills. What are they going to teach: products with certification programs, or without?
• Microsoft does it. Many, many, many people have MOUS certification and others. It's just another office suite, not wildly technical, but people do get certified as users. Why not do the same with OpenOffice.org? Some of the people at LinuxWorld who I talked to get puzzled at the very idea of training for OpenOffice.org, much less certification. "But it's so easy." Yes, to people who can do kernel programming with their eyes closed. The average user can benefit from guidance, and of course having a certification at the end of the path is a motivator for learning the new program.
• If there's a certification program for it, then it has more legitimacy. That's just the way things work. Not only is it perceived as legitimate if it has a certification program, but with that program, you get more people who know the product well. And we like that. With certification programs also come books; the publishing industry pays more attention. (Note just a couple of the many certification books produced by publishers.)
• It's 2007. Sun bought StarOffice in 1999. Eight years ago. I still have to explain to 9 out of 10 people what product it is that I train on. That's very bad visibility for an end-user desktop product. Certification isn't the only solution but I think it would help a lot. People generally need to at least know what a product is before they can consider switching to it.

Certification. It's a good thing, and OpenOffice.org needs it.

(And yes, I am very willing to work on it. But this is just a "statement about things I feel intensely about" blog, rather than a plan.)

---

---

Ubuntu all over the place

Here in the Dell theater at Linuxworld, the Ubuntu presentations Torsten's giving are getting some serious attention. He's been talking about mass deployments today.

Me, I finally got to talk about Impress and Draw, which I love. I'm not sure but I think I detected a hint of interest/delight in the ability to combine two or more 3D objects. (Cut one, then select another, press F3, paste, and move the pasted object to intersect with the other object.)

---

---

OpenProj: Open Source Microsoft Office Equivalent for Linux, Mac, and Windows

I am live blogging at Linuxworld, at the presentation by OpenProj. They've announced, today, their free desktop version of their software.

www.openproj.com

Several thousand people have already downloaded it, existing partnerships are in place, and other partnerships with Ubuntu and others are "percolating."

Regarding a server-based component, an announcement is forthcoming.

OpenProj opens Microsoft Project native files.

There is talk, at least, about some soft of bundling with OOo, etc.

---

---

Presenting up a storm at LinuxWorld

Well, the first presentation seems to have gone well. I'm at LinuxWorld in the tradeshow area, in the Dell area. Where you usually see marketing people in the Madonna headsets with multimedia presentations but in the Dell Desktop Linux section, it's me. (Trying to compete with the woman from Redhat behind me who is extremely perky and has excellent lungs.)

Anyway, this morning was OpenOffice.org Writer, and this afternoon from 3-4 I'm doing an hour of Cool Stuff I Think Is Powerful and Fun. Wednesday at 11 is Impress, and the afternoon is databases. Thursday at....I think 12 or so, I'm doing Calc, and the afternoon presentation, not at 3, is TBD. I might do advanced desktop publishing, just to see who can take it. ;>

This afternoon at 2, one of the folks with the open source substitute for Microsoft Project will be there. So come on by if you're interested in that topic.

---

---