OpenOffice.org Training, Tips, and Ideas

Using SUMIF() and IF() functions together to conditionally add different numbers

SUMIF() is great for adding different groups of numbers. See http://openoffice.blogs.com/openoffice/2009/08/using-sumif-in-openoffice-calc-spreadsheets-.html for more info on that function.

But let's say you want to add up one set of numbers in one case, and another if something else is true.

You can use IF to put together two SUMIFs. Here's a basic example.

=IF(K2="PHP";SUMIF(G2:H9;G2;H2:H9);SUMIF(G2:H9;G5;H2:H9))

Let's say you've got two developers at your web site development company. One of them specializes in PHP; the other in JavaScript and Wordpress. You want to list the revenue for projects you've done, but you want to list only the ones for PHP, if you're putting together a bid for a PHP project; otherwise you'll list the revenue for JavaScript and Wordpress.

You're putting together a template. Here's the example.

So you put together a function, IF K2 is PHP, then sum up the revenue for Bob; otherwise sum up the revenue for James.

 

Here's one with lines from the formula.

So you get this figure if it's PHP

 

And this if it's anything else.

---

---

How to make the print window for mail merge bigger in OpenOffice

It's not wildly obvious, but you can make that window bigger.

Here's it's natural state. Not that big if you want to scroll through all the records or fields.

Just drag it to the right to make it wider, and up to make it higher. I'm not positive how it works on all OSes but it seems like it should.

Queries in OpenOfice.org Mail Merge: how to print only documents for the records you want, in the order you want them

I hate it when I blithely skip by fantastic built-in features.

No more skipping by.

So you've got 2000 employees, or members, or whatever. You need to send out a letter reminding people to, oh, sign up for benefits, or renew their membership, or maybe it's just a congratulatory letter for some people who have five years of service now and are going to get that sterling silver ice bucket they've always wanted. Or let's say you need to print labels for everyone but you need to do it in order by zip code or the people at the post office are going to be very annoyed.

You don't need to set up a query ahead of time in Base or in Calc. You don't need to go into Base or Calc and sort the information. You could, it would be fine, but it's more work than you need to do.

Just specify what you want, when you print.

You know how to print a mail merge letter, envelope document, or label document. You choose File > Print, click Yes in that little window that asks you about a form letter, and you get the print window.

Check out the top row of icons.

 

Sorting

Here's what you get when you click the Sort icon. Just choose to sort by a field, up or down, then others if you need to. Just like the sort in Calc.

 

Or if you just want a quick sort-by-one-field thing, click on  column of your choice, then click the A>Z or Z>A icon to sort ascending or descending.

Filtering

To use the quick autofilter, click on a particular cell in a row. One single value, like the city where one person is from. Click the AutoFilter icon; you'll get all the records that match that value.

 

Results:

 

If you want a little more control over the filter, click the Standard Filter icon. It's just like the standard one in Calc. NOTE: The AND/OR fields in the 2nd and 3rd rows aren't enabled until you select something to filter by in those rows. It looks like they're not available, but they are.

 

And results.

 

To turn off the standard filter, click the Apply Filter icon; to get the filter back on, click it again.

You can also remove any filter or sort with this icon.

When you're done, just print as usual; I recommend selecting File, and Save as Single Document. Click OK and name the output file.

This creates a perfectly ordinary Writer document with all the fields merged, one copy of your original mail merge document for each record you chose to print by. 

Then you open that Writer document, check it over a bit to be sure it's what you want, then print that as you would any other Writer document. (The illustration cuts out the bottom half of each page for space reasons; you would have an entire page for each person.)

Using SUMIF() in OpenOffice Calc spreadsheets

SUMIF(), like other functions, works the same way in Calc as in Excel. However, it's a powerful function that I want to make sure readers know about, if they don't already.

Let's say you keep a spreadsheet full of various numbers, and they fall into a few categories. You have a bunch of things that are categorizable as:

financial items such as business expense ,donations; mortgage interest

Or perhaps
client activities such as calls to this customer; calls to another customer; calls to yet another customer

Or
expenses for various hobbies like music festivals; improv classes; book club purchases; dance lessons
or other categories.

You want to add up all the items that are in one particular category since you want to know how much you're spending on music festivals; how much on climbing related purchases; etc. But the items are entered like this. Not really well categorized. And you don't want to re-sort them to put the categories together because you're always entering new stuff, or you're sharing the spreadsheet--or you just don't want to.

So here's what you do.

You enter all your information however you want, just make sure you have a column, as shown above, to indicate the category.

Then you have a separate summary section (or placed wherever you want) for the totals. This one shows the totals already there.

How do you get the total amount you're spending on dance in January, and on climbing in April, in there?

You use SUMIF() 

Here's the syntax.

SUMIF(range containing all the categories where you entered the detail; the category you want to add up that's in the summary area such as the cell for March climbing expenses;the column of numbers you want to add up such as the detail section's entire March column)

Here's an example in the spreadsheet, with the colored squares showing the cells or cell ranges being referenced. Note that the month is March for both the result of the calculation and for the range being added; they're just not above each other.

I also used absolute column and cell references by putting a $ in front of whatever column or row should stay the same.

=SUMIF($A$13:$A$22;$A6;E$13:E$22)

This lets me drag the cell to create the calculation for every summary cell in the summary section.

If you'd like to play with the spreadsheet, here it is. Right-click on the link and choose to save it to your computer.

---

---

Quick spreadsheet tips

Just a few quick spreadsheet tips.

Right-to-Left Orientation
I just noticed this. If you like your spreadsheet weighted on the right, you can make it that way.

Right-click on a tab and choose Sheet Right to Left.

The sheet will flop and the number headings of the rows, etc. will be on the right.

Moving or Copying Sheet Content Easily Between Spreadsheets
I also want to encourage everyone to consider using the Move/Copy feature to get content of one sheet to another document. It's quicker than copying and pasting, and preserves the formatting.

Right-click on the sheet you want to move or copy and choose Move/Copy Sheet.

 

In the window that appears, select an open spreadsheet (it needs to be open already) to add the sheet to, and specify where. Also, SELECT COPY IF YOU DON'T WANT TO MOVE IT. IF YOU DON'T WANT IT TO DISAPPEAR FROM WHERE IT CURRENTLY IS, SELECT COPY.

Click OK. In the spreadsheet that you moved or copied the sheet to, you'll see the sheet where you specified it should appear.

Some cool Draw tips

I love the Effects tool. Here's how the first row works.

Rotate

Select the object, then click the Rotate icon. Red handles appear. Put your mouse around one of the corner handles; the mouse will turn to a circle surrounding the red handle. Drag to rotate.

Flip

Select the object, then click the Flip icon.

A dotted line shows the line over which the object will be flipped. You can leave it there, or drag it to flip over a different midline.

When you're ready, move your mouse close to a handle on the object, near the line to flip over. The mouse will change.

Then drag the object handle over the line, and the object will flip.

Set in 3D Object

Select the object, then click the In 3D Rotation Object icon. The line and shadowed image will appear.

If you just click the object itself right now, you get a 3D object. Fairly straightforward.

If however you drag that dotted line around

Then when you click on the object, the 3D object it turns into will look different.

Set in Circle

Select the object, then click the Set in Circle icon. Click Yes.

 

When you move your mouse over a corner, it will look like a crown.

 

Drag a corner around.

You'll see the results.

The simplest approach to the most reliable way to have no page number on the first page in OpenOffice Writer

I've posted before about how to have no page number on the first page. I used the approach of creating the page styles you used. This time I'm just going to show you how to do it using existing page styles, which takes a little of the complexity out.

(There are two easier ways, but with those ways, you lose the formatting if you save the document in .doc Word format, then close and reopen. This one works when you save in Word format.)

First: Choose Tools > Options > Writer > Printing. Turn OFF the "Print automatically inserted blank pages" option. This inserts an even page between two odds. It shows up in print preview. Don't worry about it; just turn it off unless you are doing books.

 

Now: How to have no page number on the first page, and have the page number on the second page start with either 1, or 2.

1. Create or open your document.
2. Type the content you want on the first page. You don't have to do it all, but let's say you've got some there.
3. Choose Format > Styles and Formatting.

4. Click the indicated Page Styles icon, fourth from the left.

5. Double-click the First Page page style. It will be highlighted and applied to your whole document no matter how many pages you have. You can see that it's applied in the lower left corner.

6. Click at the point in your document where you want to switch to page 2.

  

7. Choose Insert > Manual Break.
8. Select Default as the style that you want to switch to, in the dropdown list.

9. If you want page 2 to have a page number other than 2, select the checkbox and type the page number such as 1 in the box.

10. Click OK. Your second page will look like this. Don't worry about the 3/3, that's related to the option that you set earlier and won't affect page numbering.

That's the basics. You now have one page style applied to the first page and a different one applied to the second page. You can modify one without changing the other.

11. Choose Insert > Footer > Default.
12. You now have a footer in the Default page style (second page and subsequent pages) where you can insert a page number. 

13. Click in the footer and choose Insert > Fields > Page Number.

14. The page number appears. Format it as desired, center it, add the word "page" in front of it, or any other changes you want. The page number doesn't appear on the first page.

 

You now have one page style applied to the first page and a different one applied to the second page, with different formatting and footer contents applied to each. 

Define the path that an object takes in OpenOffice Impress presentations

You can use all the prefab custom animation in Impress. However, you can also control that path that an object takes. For instance, if you have a diagram and you want a small electron object to follow a certain path through the diagram, you can use OpenOffice Impress to do that.

Here's how it works.

Draw an object, then under Custom Animation’s Motion Paths tab, select any one of the first three effects.

Your cursor will change so that it will draw the kind of line you selected. Draw the path that you want the object to follow. Then run the presentation to see the effect.



To edit existing motion paths, just apply a standard motion path like Eight-Point Star or Diamond. The path will appear in the slide. Click on the path and expand it; you’ll get another path (there’ll be two diamonds, for instance). Delete the old path, and you’re good.

---

---

A good old-fashioned envelope template for OpenOffice.org Writer

You can use the Insert > Envelope feature, but this is simpler.

Just download this file to your computer. Then every time you want an envelope, just double-click it, and type in the information you want.

Then print.

Here are the two things you need to do.

One: Be sure that there are envelopes ready to feed into your printer.

Two: When you've typed in the information, make sure that your printer is set up correctly to print to Envelope 10. So choose File > Print, select the printer to print to, click the Properties button next to it, and you'll see the setup window for that printer. In a tab called Paper or something like it, you'll find a field where you pick the paper size. (It varies between printer brands.) Instead of Letter, be sure you select Envelope 10 instead. This is for normal fullsized envelopes.

Click OK, then click OK to print.

----------------
(If it doesn't work, insert the envelope different ways until it lines up correctly.)

The exciting topic of footnotes and endnotes

I realized a few weeks ago that I have rarely if ever written or blogged about footnotes. In this season of pulpy beach reads, then, let's make a 180 and talk geeky, intense, length footnotes.

Inserting a Basic Footnote

The basics are very simple.

Click where you want the number to appear, in the text.

Choose Insert > Footnote/Endnote.

To insert an automatically numbering footnote, just leave the defaults as is. Click OK.

You'll see the number in the text, and a number at the bottom of the page.

Type the footnote itself at the bottom of the page.

Now, what happens when you insert another footnote? Exactly what you might expect.

And you see the new number in the text and at the bottom, with the renumbering done automatically. Also there's less text on the page from the normal body flow since you need more space for the footnotes.

Footnotes (or endnotes) Using Special Characters Instead of Sequential Numbering

Now, let's say you want something other than plain old numbers. You can choose a special character instead; just select Special Character and click the little ... browse button. However, keep to the standard fonts, don't switch to Dingbats, since then the character won't show correctly in the document itself.

Select a character and click OK.

Then click OK again in the Insert Footnote/Endnote window.

You'll see the character in the text, and with the other footnotes at the bottom of the page. NOTE that when you select a character there is no numbering.

Endnotes

Let's talk about endnotes, now. Again, it's what you might expect. Instead of the text going to the foot of the page, it goes to the end of the document.

Select Endnote instead of Footnote.

 

The number (or character) appears in the text. NOTE THAT END NOTES AND FOOTNOTES ARE NOT CONSECUTIVE. YOU CAN HAVE ENDNOTE 1 AND FOOTNOTE 1 IN THE SAME DOCUMENT. YOU CAN CHANGE THIS IN SETUP UNDER TOOLS > FOOTNOTES AND ENDNOTES.

 

And the space to type the endnote text appears at the end of the document.

 

Changing a Footnote

To go back and change the attributes of a footnote, i.e. change it to an endnote or change the character used, right-click on the number in the text and choose Footnote/Endnote.

 

Setting up Footnotes and Endnotes

You have a huge amount of control over how the notes look and behave. Choose Tools > Footnotes/Endnotes.

 

The settings are pretty straightforward. For endnotes, see below.

 

For endnotes, the same explanations apply to a subset of the options.

Inserting Stored, Reusable Information in OpenOffice Writer: One of the Most Useful Features in OpenOffice.org

I think pretty much everyone likes to save time, and avoid painful carpal tunnel surgery. Here are the two very nice shortcut features that let you slap in a bunch of text with just a couple keystrokes.

• AutoFormat lets you create a shortcut for a piece of text like your name, your company's motto, or anything else such as special characters
• Autotext lets you put in huge amounts of text and graphics, with the formatting

Using  the Automatic Formatting to Create Handy Shortcuts

Let’s say you type the word supercalfragilisticexpealidocious a zillion times a day, or your name and title. As much as you might like to type, it's repetitive work.

Or let's say that you need to use any of the special characters like accents or Greek letters. You can insert them the long way if you want.  Choose Insert > Special Characters, scroll through, switch fonts as necessary, select what you want, and click OK. But that takes a long time.

Whatever you want to type, you can set up a shortcut for it.

To do this:

First, get the text you want to reuse ready. Like get your special character inserted in the document, or think about exactly what motto or title or name it is that you want to get into your document more quickly.

Now choose Tools > AutoCorrect and click on the Replace tab.

1. In the left-hand Replace field type your shortcut like sig and in the right-hand field.

2. In the right-hand With field, type your name or motto or paste in the special character that you've already inserted in your document.

3. Click New.

4. Click the Options tab and be sure that both checkboxes for the top item, Use Replacement Table, are marked. That just means "use the stuff in the Replace tab."

5. Click OK.

6.  In your document, type the shortcut, followed by a space, and your words or special character will appear.

AutoText: Storing and Quickly Inserting Text and Graphics

Let's say you've got a signature that looks like this.

It would be nice to just insert all that instead of typing, or even copying and pasting.

1. Go to a document where you've got that text and the logo. Select it all, including the logo, and including the paragraph above and below. MAKE SURE THAT THE LITTLE ANCHOR GRAPHIC IS NOT SHOWING FOR THE GRAPHIC—I.E. DON'T CLICK ON IT.

GOOD

BAD

2. Choose Edit > Autotext.

3. In the Autotext window, select a category, then type the name and the shortcut for it. Remember this shortcut; you can use it later to quickly insert the autotext.

4. Click and hold down on the Autotext button and choose New.

5. Expand the selected category and click on the titleof the autotext. Click the Preview checkbox below the blank area at the right. The autotext will be displayed.

6. Click Close.

 

Now you're ready to insert the Autotext. Here's how.

Long way: Click in any document. Choose Edit > Autotext, select the category and the autotext to insert, and click Insert.

The autotext will appear in the document.

Short way requiring that you remember the shortcut for the item: Type the shortcut that you entered earlier when you set up the autotext item, then press F3.

Note: To determine where your autotexts are stored, choose Tools > Options > OpenOffice.org > Paths and look at the Autotext item in the big list. Or in the AutoText window, click Paths. You can add or delete paths there.

That's all there is to it! Once you get everything set up, you might never type again. ;>

---

---

Summary

Here are some useful posts I've done lately, for a class I'm teaching.

Batch conversion of documents to PDF or HTML
http://openoffice.blogs.com/openoffice/2009/08/converting-openoffice-documents-to-pdf-or-html-as-a-batch.html

Page numbering: no page number on the first page

The Pagination extension
My post on the pagination extension.

Tri-fold brochure
This is a  template for a tri-fold brochure. It uses connected frames.  Right-click on the link and choose to save it to your computer if you'd like to look at it. Start typing in the lower right of the six frames and the text will flow.

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"/>

---

How to put a drawing into a Writer document, safely, and more quickly, in OpenOffice

When you create a drawing in Draw, you often want to then paste it into a Writer document.

However, if you just copy and paste, sometimes even though it comes in grouped together, pieces can shift.

And especially then if you resize a drawing that includes text, you're going to end up with some wackiness.

The best thing to do is to export that drawing to a PNG or other raster format file, then import it into Writer. (From Draw, File > Export, select a format, be sure to select Selection in the dialog box, and name the file. Then in Writer, choose Insert > Picture > From File and select the file.)

However, here's something that's quicker that prevents random shifting, and allows you to resize without getting weird results with text.

1. In Draw, make a copy of the drawing so you don't mess up the original. Simplest is to create a new slide (Insert > Slide), copy the drawing from the original slide to the new slide.

2. Select the whole drawing, right-click on a part of it and choose Convert > To Polygon.

3. Select the converted drawing, copy it, and paste it into Writer. With pasting, you see that the flip of the thought bubble didn't happen.

And when you resize, the text resizes.

DON'T UNGROUP THE DRAWING AFTER YOU'VE PASTED IT INTO WRITER. You can regroup it but you'll likely notice the elements separating from each other before you realize you need to regroup it.

So don't right-click on the drawing in Writer and choose Group > Ungroup. Keep it grouped, which it is by default when you paste it in.

Creating a template for postcards in OpenOffice so you don't need to redo the layout every time

This is a continuation of Monday's post on how to create postcards. See that post for how to do the exporting, positioning, etc.

So. You've created the postcards. But the positioning....while not overly onerous, is rework. And we don't like rework.

I'm not guaranteeing that this approach is a miracle but it might be nice for some people. Instead of redoing the inserting and positioning of the four graphics, you copy, then do an extra step, then export your new design over the old one.

Here's the basics. The rest of this blog provides more explanation.

All right. Here we go. Here's your succesfully created 4-up set of graphics that will print nicely onto postcards.

You need to set it up again, almost exactly the same way, but this time when you insert the picture, select Link as shown.

Then copy and paste that linked graphic three times, position as appropriate, etc.

Here's what the linking gives you. It means that the pictures aren't sitting there in the document; they're linked directly to the actual postcard.png file and when it changes, the four versions of it in your document change.

Choose Edit > Links to see this.

Here are those four links.

 

Close the window.

Here's what you do. You've got your postcardtemplate.odg file containing the four-up nicely positioned set of four PNGs for the postcards. When you're ready to make more postcards from a different design, do this. Copy that slide that has the four-up postcards in it. To do this, right-click on that thumbnail version of the slide at the left side of the document and choose Copy.

 

Then right-click in the blank area below that slide and click Paste.

You'll be asked Before or After the current slide; click After.

 

Now you've got two slides, four graphics in each, all the same.

Choose Edit > Links and you'll see eight links.

  

You want to keep the old setup with the old graphic (the barbecue chef) set up correctly in case you need to print it again and just because you want a history of what you've done. So you BREAK THE LINK to the old graphic, for that four-up slide. 

 This image shows the situation; click the image to see the text a bit bigger if you want.

 

Select the first four links and choose Break Link.

The four remaining links are still looking at the postcard.png file.

So if you overwrite that png file with, say, the contents of the birthday card design you just did, that slide that's still linked will change to show four birthday card postcards.

So go to your new design and select it, and choose File > Export. Select PNG. (Instead of actually overwriting postcard.png, you could rename it to something like design_06122009, or postcard_barbecue, or some other descriptive name.) You must name the new exported file postcard.png or at least exactly what the name is of the file that your postcardtemplate.odg file is linked to.

 

When you overwrite the postcard.png file, now the new image shows for the four-up slide that is still linked to it, and the unlinked one is unchanged. (The changes might take a bit to show up, you can close and reopen to force the change or in the Edit Links window click Update.)

 

Choose Edit > Links and you'll see it's linked to the same graphic, and there are still four links, but the graphic itself is different which forces the document to show the new design.

 

Once you've got the template set up properly linked, here's all you do each time you have a new design that you want to make postcards of.

Copy and paste a linked slide in postcardtemplate.png.

Break the links for the old design but leave four links.

Export the new design to the same file name, like postcard.png.

That's all.

Creating postcards in OpenOffice Draw

All right. Here is the big post.

We join this process in progress. You have created a new Draw drawing, using whatever techniques you wanted. Maybe you made it 8x11 for a little poster, or 11 x 17 on Tabloid paper for a bigger poster. At any rate, the design is done, it's whatever size it is, and you're ready to get some postcards out of the design.

Here it is. It's a design for the annual singing barbecue event in your town.

1. First thing. Is this design going to be the right proportions for a postcard? Postcards are 3.5 x 5 inches. You could try to do the math but it's easiest to just do this.

Select the whole drawing. Then right-click somewhere on the drawing and choose Position and Size.

Now in the Position and Size window, look at the size. It's not 5 x 3.5.

 

You're not going to change the size -- you're just going to see what the proportions are. Be sure that Keep Ratio is marked as shown and then type 5 in the height (or whatever the bigger measurement is of your drawing). See what the width (or the other measurement) is. If it's close to 3.5, then you're good. If it's not close to 3.5 then you should go back and change dimensions in your drawing to make it fit better proportionately into 5 x 3.5.

Click Cancel. This was for information only.

2.  Assuming that your design is right proportionately for a postcard, the next step is to export the drawing to a PNG or other graphics format.

Why do you need to do this?  You need to do this if you need to resize it at all. I.e. if you did it big first for a poster and now need it in a smaller format for postcards, or if you didn't think about postcards in the first place and now need to resize. Because things can get a little unpredictable if you have a bunch of different objects, including text boxes, and then you try to scale them all down to a smaller size.  If you don't need to resize then you can skip this step.

So again, select all the items in your design. You must select all the items in the design or you will get an 8x11 graphic not one the size of the design.

Then choose File > Export.

Now navigate to the directory where you want the graphic and select PNG (or if you really want JPG or another format, select that). Also be sure that Selection is selected as shown. Name the graphic and click Save.

 

If you are prompted to specify compression, just leave it at 0. If prompted for quality as with JPGs, specify 100%.

3. Step 3, you insert the exported PNG and make it the right size. (If your design is already a perfect 3.5 x 5 and you didn't need to export to PNG for resizing, then you can skip this step as well.)

In a new Draw document, or in a new slide in the old Draw document, choose Insert > Picture > From File. Find the PNG you exported. Click Open.

And here it is.

 

It's still not the right size. This time, you're going to right-click on the graphic and choose Position and Size, and change the size.

 

Select the Keep Ratio checkbox and change the longer of the measurements to 5. See how close you are to 3.5 for the other measurement.

Once you see that you're quite close, you can unmark the Keep Ratio checkbox and just set the other measurement to 3.5 If you change the proportions a bit, it's typically not going to be that big a deal.

Click OK.

Now it's the right size, if not the right position.

4. In this step we create a total of four of the designs, positioned correctly.

If your design is already 3.5 x 5, just be sure that everything in the design is GROUPED. Select all the items in the design and right-click on it, and choose Group. Then copy the design to a new slide in that same drawing, or choose File > New > Drawing and paste the design into that new drawing.

Set the margins to 0 so that when you position the items it'll be from the edge of the document, not the edge of the margin. Choose Format > Page, click the Page tab, and apply the settings as shown. Click OK.

 

Now, let's say that you need to position the upper left corner of that first graphic .5 inch from the top and .5 inch from the left. If you just click on the graphic and drag, you'll see a light line coming out of the graphic going up onto the top ruler (the X axis is a way to think about that) and onto the side ruler (the Y axis). The lines are circled below. Just move the object until it's positioned on the .5 of each ruler.

If you prefer more precision, you can go back to the Position and Size window. Right-click on the object, choose Position and Size.

Now in the X and Y axis fields, type .5 in each. That's where the upper left corner of the "Northwest" postcard should appear. (Adjust this if the paper you're using needs the postcards to be in a different location.) Click OK.

The object is positioned correctly.

Now copy and paste the graphic to get the next one. We'll use the Position and Size window again, so right-click on it and choose Position and Size.

Set the horizontal (X axis) to .5 inch plus the width of the card -- if the card is vertical then it's 3.5, otherwise it's 5.5. The Y axis is still .5 because you still want this item to be .5 inches from the top of the paper.

Click OK.

Now, copy either the PNG or the drawing and paste two more times. It will paste on top of the original so, each time, just drag the pasted one off the top of the original and put it roughly where it's supposed to be.

The bottom left item should be in a half inch from the left (X axis) and in this scenario, should be 5.5 inches down from the top (Y axis). It's .5 plus the length of the card above it.

In the following illustration (click to see the bigger version) you can see that, because it's selected, faint gray lines show on the rulers to indicate where the top and bottom and left and right are.

The following graphic makes it a bit more obvious; I've added red lines and highlighting to show that the light marks are even with the top of the object. Plus you can see, if you look at the bottom highlighting on this graphic, that the position in x and y coordinates for any selected object is shown on the bottom status bar of your Draw window.

 

This graphic shows the same thing, the lines I've added showing the bottom two.

 

When you've got four precisely lined up and positioned graphics, you're ready to print!

 

This might seem like a lot of work. It's much easier once you've done it a couple times. Plus, in the next blog I will outline a way that lets you just set this up once, then make a copy and have your export to PNG automatically get sucked into the 4-up postcard layout. So you'll never have to insert and resize and position the graphics again, just make the design, and export to PNG. It's fabulous. Stay tuned.

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.

---

---

Settings everyone should consider applying

This is a repost, but an important one. It really helps.

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

What's better, software that does what it thinks you want, or software that does what you tell it to?

If it's rocket-launching software, and I don't know much about rocket-launching, I guess I would let the software do what it wants. However, I know what I darn well want my office suite software to do. I know how I want it to behave. I know what features I want and I know, for instance, that I do not want it suggesting to me what word I am typing and offering to help me finish the arduous task of typing it.

OpenOffice.org has its default behavior and default settings just like any other software but is very cooperative in letting you customize those behavior and settings the way you want them. Which is refreshing. You just have to tell it how to behave.

So I've put together a list of the top  customizations I think are the most helpful and/or powerful. I make sure that everyone in my classes learns these by lunchtime, and review them afterwards. When the software behaves the way you want, that makes everything better.

1. Turn off the word completion.

I hate word completion, and it’s really easy to turn off. Choose Tools > AutoCorrect and click on the Word Completion tab. Make sure the Enable Word Completion option is unmarked, and click OK.

2. Turn off any automatic formatting that you don’t want.

Would you let people live in your house who you didn’t know? Then you don’t want automatic formatting going on that you don’t understand. Choose Tools > AutoCorrect and click on the Options tab. Unmark everything except the top option, Use Replacement Table. Then go back through and see if you really want anything.

(You can unmark the Use Replacement Table option too....but that table is handy, as you'll see in the next item.)

3. Use the automatic formatting to create handy shortcuts.

The same tab where you turned off word completion has a really great feature for creating shortcuts. Let’s say you type the word supercalfragilisticexpealidocious a zillion times a day, or your name and title, or anything kinda long. You can set up a shortcut for it. It's a much more reliable approach than word completion.

To do this: Choose Tools > AutoCorrect and click on the Replace tab.

A. In the left-hand field type your shortcut like sig and in the right-hand field, type the word you’re tired of typing all the time.

B. Click New, then click OK.

C. Click the Options tab and be sure that both checkboxes for the top item, Use Replacement Table, are marked. That just means "use the stuff in the Replace tab."

D.  In your document, type the shortcut, followed by a space, and your word will appear.

Note: You can also delete anything in the Replace tab that you don't want.

4. Display the icons that you want.

There are a zillion icons in OOo as with any software and you probably don’t use all of them. There’s also that dandy little result of having to click on the black arrow to get to the icons you want, while the ones you do want sit there taking up space and, quite frankly, smiling a bit smugly. So take off the ones you don’t want, leave room for the ones you do want, and add some other ones.

First step is to take off the ones you don’t want. Click on the dropdown arrow and choose Visible Buttons. Find the icons you don’t want, like double spacing, and select them. That’ll remove the checkbox by them, and that removes them from the toolbar.

Now add the icons you want. The first thing to try is to click the dropdown arrow again and choose Visible Buttons. If the icon you want is there, select it and it’ll appear.

If the icon you want isn’t there, click on the dropdown arrow again and instead of Visible Buttons, select Customize Toolbar. Find the toolbar you want to add icons to. Click Add, and in the window that appears just keep looking through the categories on the left til you find the feature you want in the list on the right. Select it and click Add.

 

Back in the customization window, you can leave the icon as is and just click OK, or change the icon by clicking and holding down on the Modify button and choosing Icon.

5. Get to know the choices under Tools > Options.

Choose Tools > Options, and you’ll see the big fat configuration window. Just as the items under Tools > Autocorrect were about default behavior, Tools > Options is about default settings, default values. Anything about the program, from icon size to language settings to where the program looks when you choose File > Open, is set here.

I suggest that you open the OpenOffice.org (or StarOffice as in this illutration) item at the top, then select Paths, and change the values for any paths you use a lot. Change the My Documents item, for instance, to change the default for where OOo tries to save documents. You’ll save a lot of time scrolling around in your Save windows.

To change the path, select it in the window, click Edit, and just point to the new location.

You might also want to expand the StarOffice Write item, select Default Fonts, and choose the ones you prefer.

When you’re done, just click OK.

You can turn off the Save for Autorecovery feature if you want--or increase the save interval
I find this feature entirely un-annoying, but you can turn it off easily if you want. Autorecovery means if OpenOffice.org crashes or you have to coldboot your machine, at least you'll have something recent to return to. If you're hypervigilant, increase the interval to every minute or 5; if you don't care about it, set it to every hour or just turn it off.

Choose Tools > Options > OpenOffice.org > Load/Save > General. You're looking for the Save AutoRecovery Information Every  option. Unmark it, or change the interval.

Click the thumbnail below to see a bigger image of the window.

Those are not all the configuration steps you can do, by a long shot. But I like them, and students seem to like them. And they're an important set of steps in the general process of showing that OpenOffice.org does what you tell it to do.

---

OpenOffice.org 3.1 outline level feature

Let's look at this document. It's a nicely organized multiheading document about Colorado tourism with five levels of headings, starting with ChapterTitle, then Heading2, and so on down. The Navigator window is shown here (if you haven't used it, press F5 to show it; it's great).

Outline Numbering is set up to match the intended use of each heading style. Chapter Title is the top level style, and so on. Without Outline Numbering being set up under Tools > Outline Numbering, we wouldn't have the Navigator looking the way it does. Or the table of contents (next) looking like that.

Here's the document's table of contents, feeding off those levels, and showing all five because I didn't restrict it to fewer.

But let's say you want some more power and flexibility. You want something in the TOC that's not in the headings.

For instance, here's a paragraph in the middle that's kind of important. Maybe you just want this one (the heading, not the corresponding bulleted list) in the TOC as well but you don't want to make it a big level 2 or 3 heading.

To do that, just select that paragraph, then choose Format > Paragraph. In Outline and Numbering, select the level you want that paragraph to appear at, in the table of contents. Then click OK.

Then update the table of contents, and you see that paragraph.

OpenOffice.org 3.1 anti-aliasing in graphics for smoother lines

For someone who loves Draw like I do, this is very nice. All the shapes you draw are now smoother in 3.1 with anti-aliasing.

I know that the reduction in the blogging software will throw this off a bit, but I think it'll be helpful. Here's the basic drawing.

And here's what it looks like at 1000% magnification, in Draw. Very smooth.

Now, keep in mind that if you're exporting to Photoshop, Gimp, or other raster programs, that you still need to be careful. The left shows the same drawing exported as PNG, the right a JPG. So far so good, or at least not terrible.

Here's what they look like at 400% magnification in Photoshop. PNG (first) and JPG (second) both look kind of icky.

If you are exporting a Draw drawing, your best best is to EPS, or even PDF.

Here's an EPS drawing exported to Photoshop, rasterized, then magnified to 400% like the others.

 

And here's an exported PDF which is openable in Photoshop as a raster file. For my money, it looks better than the EPS, at least at that magnification. It doesn't matter much if you're just using it at the size it came in at for the EPS. I do all my book covers in Draw, then export to Photoshop to rasterize and turn into JPGs for the printer.

Updating the OpenOffice.org Guidebook, and workbooks, to 3.0

Hi all,

I apologize for the delay in getting the Guidebook updated. I absolutely will and especially, though not exclusively, because it's such a great set of updates in 3.0 and 3.1. I hesitate to give a date but definitely this summer.

I have updated the workbooks to 3.0 and some 3.1 features. Not all features are included such as all the details about how to cross the Y axis at a certain point, but the procedures are included.

More Chart Options for OpenOffice.org 3.1: Placing the axes

You can now control where the axes intersect each other. Let's say you've got this data. Inserting a normal bar chart you get the data below.

 

You can modify where the axes intersect and where the labels are. Double-click the chart, then click on an axis, I'm showing the Y axis first. Be sure the axis is selected, then right-click on the axis and choose Object Properties.

Now you have various settings in the Positioning tab. You can have the axis cross at the start, the end, or a particular category. Here's what it looks like (in the next screen shot) with End selected.

The Y axis crosses the X at the far right, aka at the end.

Here's another setting and the result. You can place labels at the outside start, and other options in that list. This can be useful especially for the X axis since the bars overrun the X axis.

 

Here's what that setting looks like--outside start , or left, is the default.

 

If you want to modify the X axis, click on that and choose Object Properties.

 

Heres's what another setting looks like. You can have the X intersect the Y at a value other than 0.

 

And here's the result.

 

And another setting. You can position the labels at the outside end.

And the result, the labels are on the top instead of overrun by the bars.

OpenOffice.org 3.1 Chart Feature: What to do with zero values

If you're missing data, you have three options now: skip the data, assume zero, or compute a logical continuation.

Let's say you've got this data, with this line chart. The default is to leave the missing data out completely.

If you don't want that, though, you can double-click the chart, then click on one of the data lines, and choose Object Properties.

Select another option, such as the one setting all missing values to 0.

That might or might not be what you want.

The Continue Line option gives the best-looking, if maybe not the most accurate, results.

Three nice new features for Writer, in OpenOffice.org 3.1

I'm going to be going over the 3.1 features in the next few blogs. Here are a few that are just nice -- not groundbreaking but very nice, convenient changes that making interacting and collaborating easier. The third just makes slight annoyances go away a little more easily.

Replying to Notes
In 3.0 the notes are new and improved by being in boxes off to the side. Now you can click and hold down on the black arrow, and reply to them, too. The reply shows up immediately below the original in a contrasting color. (You need to be a different user, under Tools > Options > OpenOffice.org > User Data for this option to be available.)

Easier Accept/Reject of Changes
It's also easier now in Writer to accept or reject a change when you're tracking changes. Just right-click (as I like to say, when in doubt, right-click).

Remove Hyperlink
You could always remove a hyperlink by selecting the text and choosing Format > Default Formatting, or right-clicking on the selected link and choosing Default Formatting. However, this makes it more obvious. Right-click without selecting and you get Remove Hyperlink as an option, as well as Copy Hyperlink.

---

---

How to set up a score card for your Iron Chef party (or tabulate other voting/scoring situations) in OpenOffice Calc spreadsheets

Here's the point where MAX, COUNTIF, data validity, and conditional formatting, come together to tabulate voting results. The project also involves chocolate and squash.

I'm throwing an Iron Chef party pretty soon. It's potluck style -- I've designated the "secret" ingredients, squash and chocolate (which need not be used together) and people will bring a dish using one of them. After we eat, we'll vote for the best squash dish, best chocolate dish, best overall, most original, and best imitation of Alton Brown or Chairman Kaga.

With up to 30 people coming, though, I don't want to be in my office tabulating paper ballots while the revelry continues in the rest of the house.

So I thought to myself, how can I set up a cool (aka geeky) spreadsheet so that people can just type in their votes and the winners will just reveal themselves?

I think I'm actually end up going to use a database and a form. But this is a decent approach too, and the tabulation process (since I don't do a lot of SQL coding) is the same.

Here's the situation. I'm condensing it a bit to make it simpler but this is the essential. I've got 10 people coming, and seven people: Bob, Ellen, and others, are actually making dishes. All 10 people need to vote, reasonably anonymously, for the best dish in each of three categories. Each person randomly chooses to vote as Voter1, Voter5, whatever. They choose the person who created the best dish from the dropdown list.

That's how we start. How do you create it?

(To just download it, click here.)

Type the Voter1-Voter10, and the headings for the three prizes.

Switch to Sheet2 (just to keep Sheet2 clean) and type in all the names of the people who cooked dishes.

Now in the first blank cell under the first heading, next to Voter1, choose Data > Validity.

You're going to use the Cell List feature to create a dropdown list with whatever values are in the cell range you specify. Use absolute references as shown to refer to the range of cells in Sheet2 that contain the names.

Click OK.

The list will look something like this.

Use the small black square to drag the dropdown list cell to the other cells that need dropdown lists.

You're all set. Now let's say it's party time and people have made their votes. Or at least this is what it would look like.

Now let's set up the tabulation. First, bring in the range from Sheet2 so it's easy to see which person got the number of votes. Just type =Sheet2.A1  and drag down.

Now you're going to use the =COUNTIF() function to count, for each category/name combination, the number of votes for each member.  It's =COUNTIF(RANGE;VALUE) or, for the first one, =COUNTIF(B$2:B$11;$A16)   Be sure to use absolute references for some of the values as shown so you can drag the values to save time and effort.

Now drag the value down, and over, to fill all the cells for each name/category combination.

Now, you'll use the =MAX() function to pull out the biggest number in each column.

This is the result.

Now, you can either look for the number that MAX() gives you, to find the winner. That's all you'd need to do once all the guests have voted.

Or you could use Conditional Formatting to highlight the number that is equal to the MAX value.

Select the first column and choose Format > Conditional Formatting.

Set up the window as shown. You're going to make the cell that is equal to the MAX cell formatted using the Heading style.

Do the same for the other two columns, referencing the MAX cell corresponding to each.

Now that you've got this all set up, you'd just delete the values out of the voting table so it's blank. Then set it up on your laptop at your next Iron Chef party, and let people vote. The totals will automatically be calculated, and the winner for each category (or the people tying for first, as with one of the categories here) will be automatically highlighted. All you have to do is look at the spreadsheet and pass out the prizes.

OpenOffice.org 3.1 is out!

Lots of really great features--check it out!

http://www.openoffice.org/dev_docs/features/3.1/

How to Modify OpenOffice Calc So that the Delete Key Deletes Immediately, and Backspace or Another Key Brings Up the Delete Window

It's the little things, isn't it.

In OpenOffice.org Calc, you have to press Backspace to delete cell contents instead of pressing Delete like in Excel.

Press Delete in Calc, and you get a Delete window that lets you delete formats, text, formulas and/or other elements, a very useful feature. It just might not be quite what you expect.

Some folks note this as one of the issues with OpenOffice, like the Delete key thing.  That blog certainly isn't the only one; I'm just listing it as an example and to....well, just keep reading. I think Greg C's point in the blog is just that the Delete-versus-Backspace is an unnecessary difference from MS Office.

Would it make sense to set up the OpenOffice defaults to be the opposite way around? Sure. Did Novell swap it around in their version? Yes.  Is it still a good idea to consider switching to OpenOffice to save thousands or millions of dollars, even if your users will have to change how they do some tasks? Yes.

But honestly, is having to learn to press a different key all that big a deal? Really?

Besides. Can you change OpenOffice so that when you press Delete, the contents of the cell(s) are just plain deleted, like you expect? Yes.

Either follow these steps, or skip to the end and download the configuration file. Load it according to the instructions at the end.

1. Choose Tools > Customize, Keyboard tab.

2. Be sure that the Calc radio button is selected.

3. In the Shortcut Keys area of the window at the top, select Delete as shown. In the Functions area of the window at the bottom: under Category select Edit, and under Function select the FIRST of the two "Delete Contents" items. This is the one that just deletes, bam, with no window.

4. Click Modify. You'll see Delete now listed as a key.

5. If you don't want Backspace to delete contents, then select Backspace in the Keys list in the lower right corner and click the Delete button.

6. Now scroll through the Shortcut Keys list at the top and find a function you want to use to bring up the Delete window. It can be Backspace, or Ctrl 8, or whatever you want.

Then in the lower part of the window, as before, under Category select Edit and under Function select the SECOND "Delete Contents" item.

7. Click Modify. Your new selection will be the one that brings up the interactive Delete window.

8. If you want to share this configuration with others, click Save and name the file in the window that appears. Here's mine--right-click on that link and choose to save the link target to your computer.

Loading the Configuration File  Give the configuration file to your friends. They should choose Tools > Customize, Keyboard tab, and click Load to load that file. Then they'll be happily pressing Delete, as well.

---

---

An Open Letter to Larry Ellison about OpenOffice.org

Dear Mr. Ellison,

I'm sure there are many reasons for Oracle buying Sun. You might not be having meetings this week about OpenOffice.org. You might be having meetings next week about spinning it off into a foundation. But if you keep interest or control over OpenOffice.org, please consider the following.

You know how huge, and pervasive, the use of Microsoft Office is. You know the massive amounts of partner and corporate revenue that comes not just from selling the product but from associated services and add-on products. (The certification program alone generates a decent chunk of change.) So you know that getting a bigger market share for OpenOffice.org would be a nice benefit to Oracle.

On that note, let me make my laundry list for what I think is important for OpenOffice.org. My letter to Santa Claus, perhaps; my wish list for a win/win/win situation for Oracle, users, and of course me and other OpenOffice.org providers of products and services.

Tell them about it. 10 years after starting on StarOffice, I still introduce myself in one sentence, "I do training and educational materials for OpenOffice.org; it's like Microsoft but free and, I think, superior in many ways." Nobody looks at me weird for explaining what it is: 10 years after Sun started making StarOffice available. This is bad. Do what you have to: work with the Obama administration, go on Oprah, sponsor a Nascar driver, get it on "Lost." Do whatever it takes. We need name recognition.

Certification. I don't know who said it, but I believe it: if you measure it and reward it, people will do it. A certification program analogous to Microsoft's is essential for a variety of improvements to perception and adoption of OpenOffice.org. And it's not a bad revenue generator, either.

Partnerships and bundling. I don't need to tell you the advantages of having a product already pre-installed on hardware and included in other packages, but I'm sure not going to leave it out of this list. People who just start using OpenOffice.org without even knowing that it's not Microsoft Office also have a way easier time than those who fear change because they know it's different.

Libraries, schools, government. They don't have a lot of money. They should have no barrier to interaction. Give them OpenOffice.org. Go out and deliver it to them.

Partners. Help us. Make it easy for people like me to connect to customers. Make it easy for customers to find me and my products.  Get a big, fabulous, easy-to-use, great-looking portal web site going to let customers find providers and vice versa. Maybe make a partner certification program (optional, and affordable). Maybe make one big Amazon-type site where all partners can list their products and services. We're out there, we're just harder to find than Microsoft's partners.

It works. It's good. Don't be under any illusion that it can't compete with Microsoft from a quality standpoint. The wizards aren't there and it would be nice to have an easier way to get clip art and templates (see above re the portal web site).

I will go on Oprah with you if you can get the booking. And I'm happy to go to the White House with you, too, to demo and discuss OpenOffice.org as one of the ways to save $100 million at a time.

Thanks for reading,

Solveig Haugland

Who's using OpenProj, and how's it going?

I'm moving into some other areas for instruction and materials, including OpenProj, the alternative to Microsoft Project.

It seems like a very reasonable piece of software--type your tasks, indent the subtasks, specify the precedents, create and assign resources with various % for each task, different calendars per project and per resource, etc.

I'd like to hear from those who are using it: how do you like it, what topics are difficult and (of course ;> ) your thoughts on the needs for books (I've found one other one) and training.

Thanks!

Solveig

Oracle Buys Sun

http://www.sun.com/third-party/global/oracle/

This should be interesting.

I have no idea what to expect from Oracle re OpenOffice since Oracle is neither IBM nor Novell.

Let's keep our ears to the ground and, if applicable, our eyes on the stock price. ;>

Creating a dropdown list in OpenOffice Calc that references a list of values (repost)

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.

---

---

Using HLOOKUP and RANDBETWEEN to create random sets of things from two or more sets in OpenOffice.org Calc spreadsheets

Say you're trying to spice up your wardrobe and not just wear the same old outfits. You've got tops, pants, and shoes. You can put together a spreadsheet that randomly chooses one item from a list of tops, another random item from your list of pants, and maybe (depending on how adventurous you are) you get a new outfit you never would have thought of.

Or maybe you're a teacher or coach and trying to get your kids into different groups. You can randomly select one item from a list of captains, another from a list of cocaptains, and three or so from a list of people who just want to be on the team.

The nice thing about this is that whenever you close and re-open; or when you drag the formula down; or when you copy and paste; you get different choices because it's random.

You use HLOOKUP (or VLOOKUP) and you use RANDBETWEEN to specify the range of rows in which you want to pick something.

=HLOOKUP("Pants";D5:D25;RANDBETWEEN(2;20))

So you're looking under the column titled "Pants" in the range D5 to D25 and you're happy with being given back the value in the row that is anywhere from the 2nd item in that column to the 20th row in that column. (Note that the final parameter is relative, not absolute; if the range starts at 1 or 120, you still want to start at the 2nd item down, now the 1st or 120th. The 1st item is the column header.)

Note: There might well be a more efficient way to do this but it works. In these examples I have 2-3 separate ranges of one column each because I started out doing this with the columns in different sheets. But you could have just one range with multiple columns, so that you don't have to change the range every time.

Also: You need to use absolute references. So it would actually be:

=HLOOKUP("Pants";$D$5:$D$25;RANDBETWEEN(2;20))

And also remember that you can have the columns in different sheets, in which case it would be something like this:

=HLOOKUP("Pants";$clothing.$D$5:$D$25;RANDBETWEEN(2;20))

So let's do it. Here's the spreadsheet with all the data and formulas in it, if you'd like to look at a completed example.

First, you get your data. Put columns at the top. Let's say you want some random ideas for what clothes to wear, so put your tops in one column and pants in the other.

Then you copy over the column headings.

Then you need a formula. Here's what it would be without the absolute markers, just to make it simpler. I used the function wizard to make it a little clearer, too.

But you need $ in front of the cell references so add that. Here's the syntax.

=HLOOKUP("Tops";$I$1:$I$17;RANDBETWEEN(2;16))

Now either copy and paste, or drag, the column down to get values that are randomly chosen.

Here's the formula for the pants. Similar, except that you should change the cell references and the indicator.

=HLOOKUP("Pants";$J$1:$J$17;RANDBETWEEN(2;8))

Then drag or copy and paste down, and you get your different outfits.

Note: As mentioned before, in these examples I have 2-3 separate ranges of one column each because I started out doing this with the columns in different sheets. But you could have just one range with multiple columns, so that you don't have to change the range every time. You could set up the syntax for the tops like this:

=HLOOKUP("Tops";$I$1:$J$17;RANDBETWEEN(2;16))

And for the pants like this--note that the middle parameter, the range, stays the same.

=HLOOKUP("Pants";$I$1:$J$17;RANDBETWEEN(2;8))

However--if you have an unequal number of items in each column, then you will end up with blanks for some of your values for the shorter columns.

Another Example

You can do the same thing with captains, cocaptains, and team members.

 

Set up the data as above, then create headings. I've done them one above the other this time but it doesn't really matter. Note that the final parameters in the RANDBETWEEN are relative, not absolute.

  

And you get your results. Different every time you paste, or close and reopen.

Zillions of cool non-boxed applications

Check out all the cool software.

http://www.cnet.com/html/ww/100/2009/categories.html?tag=mncol

You can vote for the ones you like the best, or you can look at the lists for ideas. For instance, I've heard good things about "Remember the Milk" but it's been a while so I'm going to check that out. It's available for Google Calendar, offline use, Blackberry, Iphone, and probably more.

Etsy is a web site where you can sell your, and buy others', cool handmade products.

And I'd never heard of Kyte before but if you want to broadcast anything, check it out.

Using the OpenOffice.org Data Form extension to enter information in spreadsheets

Let's say that you've got a spreadsheet where you need to enter a lot of information. Here's one example, but let's say that you have lots of fields, aka lots of columns, and you have to scroll around a lot to enter the data.

What if there were a bit of an easier way to enter the data?

It's the Data Form extension. Download and install the Data Form extension here:

http://extensions.services.openoffice.org/project/DataForm

Download the file.

Then install that extension file.

After installation close both OpenOffice.org and the Quickstarter (the icon in the traybar), and start OpenOffice.org again.

Then set up the data like this, get it going.

Then click somewhere in the data, not a blank cell, and choose Data > Form. The data entry form will appear.

Using the OpenOffice.org Data Form extension to enter information in spreadsheets

Let's say that you've got a spreadsheet where you need to enter a lot of information. Here's one example, but let's say that you have lots of fields, aka lots of columns, and you have to scroll around a lot to enter the data.

What if there were a bit of an easier way to enter the data?

It's the Data Form extension. Download and install the Data Form extension here:

http://extensions.services.openoffice.org/project/DataForm

Download the file.

Then install that extension file.

After installation close both OpenOffice.org and the Quickstarter (the icon in the traybar), and start OpenOffice.org again.

Then set up the data like this, get it going.

Then click somewhere in the data, not a blank cell, and choose Data > Form. The data entry form will appear.

Doing interesting things with queries: multiplying two fields together, and summing detail records

Here are a couple interesting things you can do. Let's say you've got a bunch of customers with a few invoices each, and you just want to know how much each of them has spent. You take your one customer record and you add many invoice records together to get the total they spent on all invoices.

You can also multiply items: let's say your invoice table has the item price, and number of items purchased on the invoice, but not the total per invoice. You can multiply item price by number of items to get the total.

Open the database.

Click on the Queries icon and choose to create a query in Design view.

You'll get a window where you can choose which table to use. In this example I'll use the invoices table. Click Add to add the table.

Then double-click a few fields to add them. I'm going to add a few here, then delete some later.

Now I've got the fields added.

If I click Run Query then I just get those fields.
 

Note I have the unit price and number of items but not the total per invoice. Here's how to do that. Just type as shown in the far right column. It is case sensitive.

Clicking Run Query gives the results.

Which look like this--you get the Alias you typed, not the multiplication. Note however that it is not formatted as currency in this example. If you want to change it you can right-click on the column heading, choose Column Format, and select a currency or number format.

And here's what that looks like.

Here's a note. You don't have to have the fields you're multiplying in the query. You can unmark the checkmarks as shown and they won't show up. Or you don't even have to have them at all; you can right-click on any column head and choose Delete to remove it from the query. (Do this in the bottom area of the query where you do the defining, not the top where you see the results.)

Now you're thinking about doing that sum thing, adding up all the totals for all the multiple invoice for each single customer. For this, you need to have just the customer ID (one) and the calculated field (many for each one customer). So I deleted everything but those two columns.

Then for the single field I selected Group in the function list, and I select Sum for the many field, as shown.

Run query, and here are the results. Note that now each customer is only listed once, and the total for their invoices is calculated.

 

IBM Purchases Sun

All the rumors have finally happened. IBM has purchased Sun. As far as what will happen to OpenOffice, it's anyone's guess. However, a key strategy that IBM VP of consumer software Roy Hanson has mentioned in the past is advertising OpenOffice.org to its target market. This is considered a controversial move that would not happen quickly.

Other possible changes being bruited about are a partnership with Oprah to create and be featured in Oprah's Software Club, and a televised version of the open source software development model hosted by Kathy Lee Gifford.

Printing a group of certificates using mail merge, Draw, and Writer

You've had a contest, or a class, or something where you want to give out certificates of recognition.

Normally you would do this in Draw.

But there are 435 people to give certificates to and darned if you're going to type in 435 names by hand when you already have the list of names in a spreadsheet. (Or some type of list.)

So here's what you do.
1. Create the certificate, maybe just download it off the Internet or else create or embellish your own certificate design in Draw and export it as a PNG graphic
2. Make the certificate graphic the background of a new landscape Writer document
3. Set up a mail merge with the spreadsheet of names as your data source
4. Drag in the Firstname and Lastname  from the data source into your Writer document and add any other text, like "Congratulations for Completing" and the date, course, or other information
5. And print

1. Create the certificate.

You can just google +certificate +image  or other parameters such as +free and find a lot. Try Google Images. You might not find something free but you might find something for a dollar that you can use again and again.

Download the image to your computer, then create a new Draw document and choose Insert > Picture > From File to bring the picture into the Draw document. Or just copy and paste it into a Draw document.

Make sure it's the right size, so choose Format > Page, Page tab, and select Landscape. Click OK. Then drag the corners of the document so that the picture fills the whole printable page space.

Make any changes you want to the graphic such as making it redder, making it gray scale, whatever. When you click on the graphic you get the options in the upper left corner of this drawing.

When it looks like you want it, select it and choose File  > Export. Select PNG and select Selection, and click Save.

When prompted for compression, choose 0.

2. Make the certificate graphic the background of a new landscape Writer document 

Create a new Writer document.

Choose Format > Page, Page tab. Make it Landscape, with the minimum page margins that your printer can handle. 

Then click the Background tab. Select Graphic instead of Color at the top, then browse to find the PNG file that you exported. Select Position. Click OK.

 

Make the document look the way you want and leave a space for the name.

 

Be sure to save the document.

3. Set up a mail merge with the spreadsheet of names as your data source
See this blog entry. You have the spreadsheet so create a database file that points to it.

http://openoffice.blogs.com/openoffice/2007/01/mail_merge_in_o.html

4. Drag the names onto the Writer document.

Continue the process in the blog by viewing your data sources (View > Data Sources or press F4) and select the data source you created, then expand it and select Table and the sheet name. Drag the field names (not the data but the headings of the fields) into the place on your Writer document where you want them.

http://openoffice.blogs.com/openoffice/2007/01/mail_merge_in_o.html

5. And print.
When you print, follow the printing instructions for mail merge. File > Print and when the message appears asking if you want to print a form letter, choose Yes.

Choose to print to the printer to print immediately, or (better) print to another Writer file first to see what it would look like. Then print that exported Writer file. To do this, choose File, then click OK.

When prompted for a file name, type something like printed_certificate_file  .

The files will print for a while. Then choose File > Open and open the the file you created, the printed_certificate__file. It will look something like this, with the real data in it.

Once you've done this once, you can use it over and over. Just put different data into the spreadsheet and the certificate document will draw that new data into the spreadsheet.

How to make text wrap inside an object in OpenOffice.org Draw using the "Word wrap text in shape" option

I'm sitting here doing a site map for a web site. I want to make some shapes with text in it. Some of the text might wrap within the box. That would be nice. But when I draw a shape I just get this with no wrapping.

And when I right-click and choose Text

...and go to the Text tab, the option for wrapping is grayed out. I click everything I know how to click and nothing changes.

I soooooo don't want to hit the Return key every time I need a break in the line.

I googled around a bit and guess what? There are two kinds of rectangles, of course, but the regular ones don't have the wrapping powers and the others do.

Don't make rectangles with the far left tool; make them with the indicated tool.

So now when I draw a rectangle (or other shape) from the palette o'shapes, it starts off the same. But when I right-click and choose Text and go back to that tab, the option is enabled.

and when I type more, it wraps beautifully.

Though if I keep typing and typing it pops out the top and bottom. Which might be what you want.

But if you want the box to expand vertically to hold the text, as with say a big paragraph, then you would want this option.

And the box will expand.

Note: What if you just want a text box and not a colored-fill box? I know that the text box used to have the option to wrap, but I'm a little mystified because it doesn't now. The hack is to create one of those shapes from the palette, type in it first, then take out the fill and make the border invisible.

How to not have to do this every time for every box.

You can change the properties of the Default graphic style by choosing Format > Styles and Formatting. Find the Default style, right-click and choose Modify, and in the Text tab select the option for wrapping, as described above. Click OK. Now all future objects IN THAT DOCUMENT will wrap without your having to modify them.

If you want to not have to do that in every document, make the style change then choose File > Templates > Save, and name the template, noting also the category which is probably My Templates. Click OK. Then chose File > Templates > Organize, open the category in that window, find the template you just created, right-click and choose Set as Default Template.

Taking March off

Hello all,

I have been dealing with a death in the family and so have decided to take off the rest of March (possibly less but possibly the whole month) to give myself some time to rest.

I will try to respond to some blog posts.  Apologies for not being responsive lately; I find that I need  to have fewer things to think about while I get through this time.

Regards,
Solveig

Using named data ranges to have a more readable reference to items in formulas, in OpenOffice.org Calc spreadsheets

Who doesn't love a formula like this?

=(E18*F25)/((AVERAGE(C23:C20)) + SUM(D18:D29))

Now, the math is fairly complex and I just made it up to look extra complex, but just looking at it, you're not sure what it's referring to.

If you used names, which let you give more clear, descriptive names to parts of your spreadsheet, it could look like this.

=(TotalSales*TotalMonths) / ((AVERAGE(2006Sales)) + SUM(SalesBonus))

Ahh.....that's a little clearer. (Since it's a random made-up example, it might not make total sense from a business standpoint, but you're at least in the ballpark now.)

That's all very well, but how to do you add the names?

You can assign any name you want by selecting one or more cells and choosing Insert > Names > Define.

Type the name, click Add, and click OK.

Then you can create formulas like this.

Note that you can't do this:
=Ement+Admit

because those names refer to ranges. You can't do  =Ement+Admit because you can't do =B17:B20+E17:E20

You'd need to create more names to refer to just the totals. You'd create another name for the total field where the =SUM(Ement) total is, so that then you can type =TotEment+TotAdmit  (total Entertainment Expenses plus Total Admission Expenses).

 

There's a quicker way to get all your columns named. Let's say you've got this data.

Select all the data and choose Insert > Names > Create. In this case it makes sense to select the checkmarks for top row and left column since that's where the labels are. Then click OK.

To look at the names created, choose Insert > Names > Define.

Now you can create totals, for instance, like this. Note that when you're in a formula and you start to type a name, it will appear in the Formula field as Prepaid_Taxes does here. Press Enter to accept it so you don't have to type the whole name.

As mentioned before, you'll probably want to make fields to just refer to the totals. Select the cell and choose Insert > Names > Define. Type the name, click Add, and click OK.

And then you can do something like this for the formula. (Yes, you might do a SUM(range) for this but let's say that you've got all these amounts, Bus exp and Salary etc all over on different sheets or farther apart, so you would have to do the + instead. )

and get a result.

Tip: If you want some visual help figuring out where all those names come from, now that you don't have the cell references, you can choose Tools > Detective > Trace Precedents.

---

---

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

Remember to install extensions for OpenOffice.org

The flexibility of open source, and the handy Extensions system, lets the community write functionality for OpenOffice.org without having to make it part of the main program.

I love extensions. If you haven't looked at them, check them out. Installation is insanely easy, and I consider several of them essential:

• Import/Export to Zoho or Googledocs
• PDF Editor
• Pagination extension that lets you easily put no page number on the first page and page numbers on the 2nd and subsequent pages. (Here's my loopily excited blog about it.)
• You might also want to check out the Sun report writer extension -- very powerful but I haven't used it yet. Here's my blog on it.

Here's everything I've written about extensions--at least, everything I've written about extensions that I've categorized that way.

Here's the specific blog on how to install extensions.

Installing Extensions in OpenOffice.org

Installing extensions is pretty easy. Download the extension.

Then in OpenOffice.org, choose Tools > Extension Manager.

Select My Extensions and click Add.

 

Find the extension file you downloaded.

An installation process runs, and you’ll see Enabled next to the extension. For some extensions, you’ll need to restart OpenOffice.org.

To use it, look for a new menu, new menu items, new toolbars, or all three. Often there's a toolbar or menu called Add-On.

Installing Fonts so They'll Show Up in OpenOffice.org

I've gotten a few questions about this lately, so wanted to post briefly. I'm not a fonts expert, but I just copy fonts files into the Fonts folder on my computer such as Windows\Fonts  and it works. Sometimes I need to restart OpenOffice.org or, if that doesn't work, restart the computer. But OpenOffice.org does pick up the fonts you have on your operating system.

If the font shows up in the list but doesn't look right or print, choose Tools > Options > OpenOffice.org > Fonts and check to see whether a font substitution has been set up.

Making a Specific Template Come Up When You Create Any New Document, in OpenOffice.org Writer, Calc, Impress, or Draw

Anytime you can help users do something without actually have them do anything is great for them and great for you.

Templates are a great way to save time. Set up templates with the styles, graphics, etc. that people need, and they don't need to re-create them. (Or create them in the first place.)

However, getting users to use the templates is another step. For them, choosing File > New > Templates and Documents might not be something they'll want to do or remember to do every time.

What if one of the templates you've created is one that many or all users use all the time as the basis for new documents?   You can make it come up when users just choose File > New > [type of document] by setting it as the default template. I.e. the user uses the template but doesn't even need to select it.

First, create a new document and make it how you want it: create styles, apply styles, include canned text, whatever.

Choose File > Templates > Save. Select a category and name the template. Click OK.

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]

---

Things that don't work when you save in .doc format

OpenOffice.org has a feature that lets you automatically save documents in Microsoft Office format. Save Writer as Word, save Calc as Excel, etc. This lets your users send out documents to the outside world without having to remember to save in MS format first.

Choose Tools > Options > OpenOffice.org > Load/Save and use the lists in the bottom half of the window.

Likewise, you can save an OOo document in MS format by choosing File > Save As, or by choosing File > Send > Document as [microsoft format for that document type].

However. There's a but. It's not a huge but, but it's significant.

In some versions of OpenOffice.org, the following features don't work when you save a Writer document in Word format. It's not just the automatic saving, it's saving in Word format through File > Save As, as well. So the only way to get around it is to save as PDF (click the PDF icon on the toolbar or choose File > Export as PDF).

This isn't a complete list; please add your own through comments. I just tested all of these on the standard 2.3 release.

Mail merge prints field names, not content. If you save your mail merge document in Word format, then print, all you'll get is the names of the fields, like Firstname, rather than the data, like Bob.

Background graphics disappear. If you choose to put a graphic in the background of a header footer or page, under Format > Page, the graphics will disappear when you save in Word format.

Custom frame, page, and list styles get screwed up This one is an issue because page styles are the basis for doing so much really good, powerful stuff. (I hope this is on the list of things to fix, and/or not a problem in other builds.) I just tested, in 2.3, a file I'd created with custom page styles to automatically switch from a page style with no page number on the first page footer to a page number on subsequent page footers.

If you insert page breaks between page styles with Insert > Manual Break, and the  Break With feature in the Text Flow tab of a paragraph style, do preserve more formatting than the Next Style feature in the Organizer tab, which preserves nothing.  The headers and footers are preserved in the first two approaches, as are page borders and jumps from page 2 to page 66.  Landscape versus portrait is also preserved.

However, no background formatting is preserved, and the page style names are changed to Default for the first one, Convert1 for the second, and so on.

If these are issues for you, please vote to have the following bugs prioritized as things to work on. (I'm actually having trouble bringing up the OOo issue tracker right now; I think these are correct but I will check them later.)

http://www.openoffice.org/issues/show_bug.cgi?id=78723

http://www.openoffice.org/issues/show_bug.cgi?id=73533

http://www.openoffice.org/issues/show_bug.cgi?id=11522

http://www.openoffice.org/issues/show_bug.cgi?id=22635

---

---

Making Illustrated Callouts With Clip Art From the Gallery, and the OpenOffice Callout Drawing Tools

I love the callouts in OpenOffice.org. I also love that they're available in Draw, Impress, Writer, and Calc.

To get started, just choose View > Toolbars > Drawing. If there isn't a checkmark next to the Drawing option, select it.

Now look for the Drawing toolbar. It usually appears at the bottom of your work area.

Adding a Graphic

You can choose Insert > Picture > From File to add a graphic if you know where it is. Or just choose Tools > Gallery to turn on the picture gallery, and drag in a graphic from there. Click here to learn more about using the Gallery, including adding graphics to it.

Adding a Callout

You can choose Insert > Picture > From File to add a graphic if you know where it is. Or just choose Tools > Gallery to turn on the picture gallery, and drag a graphic into your document.

1. Be sure you've got the Drawing toolbar on, according to the instructions at the top of this blog.

2. Find the Callouts icon.

3. Click and hold down on the black triangle to the right of the Callouts icon.

4. Click on the tool you want. Move your mouse into the drawing area.

5. Click and hold down, drag your mouse down and to the right (or up and to the left) and you'll see the callout being drawn.

6. Release the mouse, the callout you drew appears.

7. Select a different fill color for the callout.

8. Select different line width, style, and color for the callout.

9. Double-click in the graphic; the cursor will appear.

10. Type the callout.

11. If the text is too big, click the callout border, find a handle, and drag the handle to resize the callout.

12. Double-click in the callout to select the text so you can change the font, then select a font from the dropdown window.

13. Drag the point of the callout to where it needs to be; it will change sides if necessary. Alternately, drag the callout to a different position in relation to the graphic.

And you're done!

From Draw or Impress: Exporting the Graphic to Reuse Later (for Use in the Gallery, on a Web Page Like This Blog, or Any Other Reason)

You can copy and paste to Writer or Calc. However, if you want to add your graphic with callout to the Gallery, put it in a web page, or anything else more reusable, do this.

Select everything you want to export. You can select everything manually, press Ctrl A, or just draw  a box with your mouse (not the box tool) completely around the items to select.

Choose File > Export.

Specify the location to export to and name the file. Select the format, not HTML but JPG, PNG, or something similar.

Click Save.

If a message prompts you to make some choices, either click OK accepting the options, or make your choices. For instance, with JPG, the highest number is the best quality.

---

---

Times Tables Quiz Sheet in OpenOffice Calc: Two Versions

For one of the exercises in my training materials, I put together an exercise using the random number function. It's a times table document that regenerates itself every time you open it.

Click this image to see a larger version. The first number in each multiplication is fixed, and the second is random.

Here's the file if you just want to download it.

I did it by entering 1 - 12 in three columns. I just typed 1 in the top cell, then dragged the small lower-right-corner of the cell down to create a series of 1-12.

I did the same with the x for the multiplication sign.

And I put this formula in each cell where I wanted a random number between 1 and 12 to come up.
=RANDBETWEEN(1;12)

The document regenerates with different multiplication questions in different order each time you open it, so students can't remember which answers were where. Print the document to PDF if you want to preserve it in a particular way.

I've created another version here, a random number between 1 and 25, multiplied by another random number between 1 and 25.

For other ideas, see Plugged In Home School for a tip on making a word find puzzle.

---

---

How to insert graphics in OpenOffice Writer that are reasonably manageable

I sat down a few weeks ago to figure out the best way to anchor pictures.  Specifically, how to do it in a standard context like this. You have a paragraph or line of text describing something, and you might then say "....as shown in the following illustration." Then you have a picture on a line by itself. No wrapping or anything, very simple. Like this.

The horses at the Kalispell Dude Ranch are well-trained and gentle.

A very reliable way to do it is by anchoring  As Character.

Anchor As Character treats the graphic like a letter. Which means you can't drag it around, but that you can position it using the text alignment and indent features. And the less dragging, the less it is likely to be a little Skittery.

How to Use Anchor as Character

Step 1: Insert the Picture.

Create a blank line for the picture by pressing Return after the text. Choose Insert > Picture > From File and find the file (or just drag the picture in from the Gallery).

Step 2: Anchor the Picture As Character.

Right-click on the picture and choose Anchor > As Character.

Step 3: Position the Graphic Horizontally

Click NOT on the graphic but in the empty line NEXT to the graphic. Then just use the Left, Center, or Right alignment icon on the toolbar.

Troubleshooting: If the picture shows up about a quarter inch high, that's because the paragraph style applied has a height max on it. Nothing wrong with that, it's just not suitable for a style that's applied to the line a picture is on.

Either:

* Click in the paragraph marker next to the picture and choose Default from the dropdown style list

* Or click in the paragraph next to the picture, choose Format > Paragraph, Indents&Spacing tab, and select Single as shown.

Step 4: Position the Graphic Vertically

To control whether the graphic is vertically at the top, middle, or bottom of the blank line, right-click on it again and choose Align > Base At Top, Middle, or Bottom. You don't have to do this if you think the way it looks is fine.

Optional 1: If you want to make sure that the graphic automatically stays with the previous line of text describing it, here's what to do. Select the text above the graphic, choose Format > Paragraph, Text Flow tab, and select Keep With Next Paragraph.  Be sure to do this only with the paragraphs that precede graphics or you’ll end up with unusual pagination.

You might decide to create a specific style that you use for paragraphs that precede graphics, and define that style with the Keep With Next Paragraph attribute.

Optional 2: Click on the line, on the same line as the picture, and choose Format > Paragraph, Indents&Spacing tab. In the Spacing section in the middle, you can set spacing above and below the picture to space it so that it looks good and not too crowded up against the text.

Optional 3: Click on the picture and choose Insert > Caption to create a caption that stays with the picture.

The text, with a numbered field, will be inserted next to the picture, and the picture AND the new caption will be encased in a frame. That's how the caption stays with the picture.

Other Tips

A Setting You Probably Really, Really Don't Want

Here's what not to do. Right-click on the picture, choose the Type tab, and unmark the Follow Text Flow setting.

That will give you this result, with the picture going outside the margin. Yuck.

What About Anchor to Paragraph?

Anchor to paragraph is OK, as long as you don't unmark the Follow Text Flow option above. (I.e. keep that option marked.) You can drag the picture around with Anchor to Paragraph. But anytime you have that amount of freedom, it's possible to accidentally put the graphic where it shouldn't be. So I recommend trying Anchor as Character for solid graphics that stay where you put them.

---

---