OpenOffice.org Training, Tips, and Ideas

Using Vlookup() (or Hlookup()) in OpenOffice.org Calc spreadsheets, with a Data Validity dropdown list

For all you spreadsheet users: here's something kind of cool.

Let's say that you have a set of data. You have a list of items, and for every item that there is a unique item number, category number, and packaging type. (One row and three unique columns.)

Or you have been getting your home entertainment organized and you have a perfect system for throwing parties: for every main dish there is a specific drink, appetizer, dessert, and game.

Having the data isn't the trick. What the data lets you do is that elsewhere in your spreadsheet, you can type or select the first item from a list, and have one or more of the other associated pieces of data pop into the cells next to it. Select the main dish and you also automatically get the associated drink, appetizer, dessert, and game.  

You use =VLOOKUP() OR =HLOOKUP to do this.

Here's an example. I have this data. There are several columns but here are the first two.

Here's one thing about the data. Be sure to sort it. Sort it by the first column, alphabetically or numerically. Select all the data, choose Data > Sort, and sort as usual.

At another spot in the spreadsheet I can set this up so that when I type "Beans and rice" in cell C19, the formula here.....

will automatically display the right type of drink for beans and rice (that I have set up in the data set).

 

How does the formula work?

($C19;$A$10:$D$15;2)

The first part $C19 (the $ is just an absolute reference) is the cell containing the value that I want to look for in the FIRST column in the data set. In case the type of food such as beans and rice.

The second part is the range of data.

The third part is the column containing the data I want. I type 2 for the drink; 3 if I want to display the column containing appetizer information.

You can keep on going by adding more columns. Use the same formula but set it up so that the last argument (the column) is 3, 4, and 5 respectively.

 

and that's how this looks.

If you're thinking that typing the names of the dishes is a lot of work, especially if instead of six main dishes you had 122 part names or numbers, you're right. Ideally you'd set up a dropdown list.

Click in the cell where you want to display the first piece of data, the main dish. (You probably wouldn't make the lists and VLOOKUP positioned right next to the original data set; I'm just showing them side by side because it's simpler, and it's frankly easier to get screen shots this way. ;> )

1. Choose Data > Validity.

2. From the type list select Cell Range, then type an absolute range (with $row$column format) as shown, around the column of labels.

 

2. Click OK.

Then click the little tiny black handle in the lower right corner of that cell where you made the list, and drag it down to put in a list in other cells too.

Now you can just select something from the list, and all the corresponding info, from the data set, will appear in the cells where you've also put the VLOOKUP formula.

(HLOOKUP is the same, but rotated 90 degrees.)

Getting rid of the blanks in a spreadsheet column or row, and more

Note: As Karl pointed out in a comment, this is actually a complex solution. It is a good solution for two or more columns, but if you have one column as shown here, the simplest approach is to just sort the column and the blanks and cells with one space in them will be grouped together. Delete those cells and you get the same result.

I'm going to a big blues dance event this weekend. The people signed up are listed on EventBrite, so since I wanted a list of people coming, I copied the list and pasted it into OpenOffice. But when I pasted the list to a spreadsheet, I got a bunch of blank cells. I want to have a nice singlespaced list of the names.

 

 By the way, Edit > Paste Special, Skip Empty Cells, won't do what I want. It's a badly phrased option and has to do with whether you overwrite when pasting.

Anyway--so to remove the empty cells I selected the column and chose Data > Filter > Standard Filter. I made sure that the Name column was selected, left = selected, and selected Not Empty.

 

I clicked OK and....voila. Voi not, that is. 

That pesky EventBrite site puts in a space, just a single space, in some but not all of the empty lines. Why? Well, why not. I discovered this by clicking in the remaining seemingly empty cells, and yep, there was a blank space.

 

So now I need to screen out not only stuff that's not empty, but stuff that doesn't have a space as the only content. That's a little hard....and I don't want to search and replace to remove spaces, since then I'll end up with TrentPrice instead of Trent Price.

I went back to Data > Filter > Standard Filter and filtered out everything that STARTS with a single space. 

 

And that worked great.

 

Be careful of "templates"

I've been writing about how you can use free templates created for Microsoft Office in OpenOffice.

BE CAREFUL WHAT TEMPLATES YOU PICK. SOME OF THEM ARE WORSE THAN USELESS.

This has nothing to do with what software you're using -- it just has to do with being careful.

I was just looking around for an example to use and came across a template chock full of nested numbering that DIDN'T USE THE NUMBERING TOOLS. The numbers and letters were all typed. 1, 2, 3, so that if you hit enter at the end of an item, you'd have to type in the next number, 4. If you needed to insert an item in the middle of a list, you'd need to RENUMBER EVERYTHING AFTER IT.

So be careful what you pick. Just because it's called a template doesn't mean it will help.

Free Microsoft Office templates for Word, for use with OpenOffice Writer

As I posted yesterday, you can use OpenOffice.org with most of the templates out there. Writer/Word, Calc/Excel, and Impress/Powerpoint. Also remember that OpenOffice opens .docx files without any extra help.

So google away, find your Word or Excel template, download it, and follow the steps in yesterday's post to save it in OpenOffice template format in your own template collection. Here's one of many sites. http://www.docstoc.com/

Here's the only difference: it's after you've saved it. Instead of being able to access it the way you do with Impress, you choose File > New > Templates and Documents to get it.

 

Then you'll see a window with all your templates. It might be in a folder of templates like My Templates or it might be at the top of all your folders. You can tell by the info in the title bar. The template will be in the folder you picked when you chose File > Templates > Save. Then just double-click and you're good.

 
 

Quick new presentation templates to use in OpenOffice Impress

As you know, OpenOffice doesn't come with the dazzling depth or array of templates that Microsoft Office users get. However, here's the thing. 

A) Templates aren't software. You can get great free software, without many templates, then go out and get the templates separately, also for free.

B) You can use Microsoft Office format templates to your heart's content in OpenOffice.

So here's what I just did.

1. Googled free Microsoft Office templates.

2. Picked this of the many results. http://www.presentationmagazine.com/free_powerpoint_template.htm

3. Downloaded this one (a dear friend just got married to the man I set her up with -- it seems an appropriate choice. :) )  http://www.presentationmagazine.com/wedding_powerpoint_template.htm

4. From OpenOffice, chose File > Open and opened the downloaded file.

 

5. Now, I can just use it, save it as a presentation, do whatever I want at this point. However, if I want it in that nice little catalog of templates that pops up when I create a new presentation....

I choose File > Templates > Save and name it, then click OK.

 

6. And now (without restarting or anything) when I choose in OpenOffice File > New > Presentation, that wedding template is right there in My Templates.

 

Combining and summing fields in OpenOffice.org database queries

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

 

OpenOffice 3.2.1 is out

Download it at http://www.openoffice.org.  Release notes are here. 

http://development.openoffice.org/releases/3.2.1.html

It's a bug fix release, and also features new branding from our new benevolent overlords. ;>

Adding clip art to the OpenOffice gallery

It's nice to be able to just pull clip art from within the program. Some users find it difficult to navigate a file system, which is what you need to do if you're using Insert > Picture > From File. And, frankly, dragging from a nice pane of graphics is easier.

To get a bunch of graphics, just google for free clip art and download it.  Here are a few options specifically for teachers.

Some clip art

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

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

You might also consider the phenomenal Big Box of Art, vast amounts of clip art for not a lot of money.

So whether you want easier access to pictures or video, or good change management techniques, the gallery is a nice feature.

The locations of all the gallery graphics and other gallery info is stored in openoffice\share\gallery, in the .sdv files. Copy those files to other computers if you want them to have the same graphics, when you're done doing the configuration.

1. Click the Gallery icon or choose Tools > Gallery.

2. The gallery will appear.

3. Click New Theme.

4.  Click the General tab, and type the name of the theme that you want displayed in the Gallery.

5. Click the Files tab and click Find Files.

6. In the window that appears, go to the directory containing the graphics. The directory can contain subdirectories; all graphics in all subdirectories will be brought in for you to choose from. Click OK.

7. All the graphics in that directory and any subdirectories will be brought into the window.

8. To add only some, use Ctrl select or Shift select to select specific graphics. Select the Preview checkbox to see graphics. Then click Add.

To add all graphics, just click Add All.

9. A window will show the graphics being added.

10. Find more graphics and add them the same way, or just click OK. You'll see the graphics in the gallery. To add a graphic to a document, just drag it into the document.

If  you need to change the name or add files later, right-click on the theme and choose Properties. This will take you back to the same window.

---

---

How to select an image that you put in the background of an OpenOffice Writer document

It's easy to put an image in the background, behind text. Right-click on the image, choose Wrap > In Background.

But once it's back there......you can click all day long and not be able to select it to move it, make it lighter, *delete it*, whatever.

UNLESS.....you RIGHT CLICK on the image. Right-click on it and the green handles show up. Then you can left-click on it and drag it, etc.