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.

 

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

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

Name    Fee

Bob      $12

Jean    $13

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

Name: <Name>

Fee for license: <Fee>

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

Name                          Fee

Bob                             $12

Jean                            $13

Library                         N/A

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

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

Here's the spreadsheet.

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

 

So: in the scenario I outlined you could:

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

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

Name                          Fee        Applicable or Not

Bob                             $12      [leave blank] 

Jean                            $13      [leave blank] 

Library                  [leave blank]       N/A

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

Name: <Name>

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

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

Formatting OpenOffice spreadsheet cells with DD/MM/YY

Things don't always work the way you expect. However, it's easy to make things work the right way with spreadsheet cells once you know how it works.

Let's say you want the day first, so that 12/1/10 is January 12th, not December 1st. You do this under Format > Cells. However, if you just change the format to DD/MM/YY manually, you might get some unexpected results. It's usually better to just switch the country/language for those cells.

Here's why. We'll start out "normal" with standard American English formatting, MM/DD/YY.

And now I switch the format code to DD/MM/YY manually...

But now when I type a new date into a cell I've formatted that way....

So switch the language to one that uses the date format you want. Then what you type stays in the same order.

Switching Between Absolute and Relative Cell References in OpenOffice.org Calc Spreadsheets

If you want to refer to a cell absolutely, you type $A$1 instead of A1. This means that if you drag down the cell or drag it over, that it doesn't change to A2 or B1.

Let's say, though, that you realize you need to switch from absolute references to relative, or vice versa. You could laboriously retype everything. However, there’s a quicker way. To switch from relative to absolute or vice versa, select the cells containing the formulas and press Shift F4. You’ll see in the illustrations how it cycles through.

Managing ANDs and ORs in the OpenOffice Calc Standard Filter

The Standard Filter is a nice tool. But when things get complicated, it's about the logic, not the software. Make sure you keep the logic solid as you build the filter.

Let's say you've got this list of people. We'll keep it short just to simplify the example.

 

You want to build a filter that will filter OUT everyone EXCEPT:

people from Kalispell whose names are also either Hanson or Miller.

Taking a look at the data, the highlighted names are the only ones who fulfill the full criteria.

There are other people named Hanson, and Miller (and Hansen) but these are the only ones with the right names who are also from Kalispell.

So select the data, choose Data > Filter > Standard Filter, and you might build this filter.

 

Hanson, OR Miller, AND Kalispell. 

But you get this data, because what the filter really says is

Hanson

OR Miller AND Kalispell

What you need to do is repeat one of the criteria, so you have two sets. Like this:

 

This says

Kalispell AND Miller

OR

Kalispell AND Hanson

So you get this correct result.

In OpenOffice 3.2 there are eight spaces for your filter criteria, so you do have the room to put in all the necessary logic.

"Contains" and "Starts With", and more, now in OpenOffice Calc 3.2

You could do it before with regular expressions, but now you can have more control in filtering with:

• Contains
• Does Not Contain
• Begins With
• Does Not Begin With
• Ends With
• Does Not End With

From the spec: "The "Standard Filter" dialog now shows 4 instead of 3 conditions, and has a scroll bar which allows to access up to 8 conditions in total. It now supports the filter conditions 'Contains', 'Does not contain', 'Begins with', 'Does not begin with', 'Ends with' and 'Does not end with'."

So you can look for Hansen and Hanson but not, say, Senngard or Pensonner, by using "Ends with" and an OR.

(Note that as always you have to be careful....you would also find Manson with this, so you might want to just look for = Hanson or = Hansen.)

In my happy place: reading list of 3.2 upgrades

Download the official release for OpenOffice 3.2!

And here are the features.

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

All right. This will take a few posts but let's start talking. This isn't the complete list, see that above, but it's the stuff I'm excited about.

General

Faster startup times! Haven't tested but the graph looks great. 

Some file support stuff: MS Office 2007 passwording is supported. That's a nice reassuring feature. Also 2007 pivot tables are supported.

• Password protected Microsoft Office XML files (supported document types: MS Word 2007 documents (*.docx, *.docm); MS Word 2007 templates (*.dotx, *.dotm); MS Excel 2007 documents (*.xlsx, *.xlsm); MS Excel 2007 binary documents (*.xlsb); MS Excel 2007 templates (*.xltx, *.xltm); MS Powerpoint 2007 documents (*.pptx, *.pptm); MS Powerpoint 2007 templates (*.potx, *.potm)).
• OLE objects, form controls and pivot tables can now be read from MS Excel 2007 documents (*.xlsx, *.xlsb).

And fonts galore are now available. "There are many high quality commercial and free OpenType fonts that are based on Postscript outlines. They are now supported for formatting, printing, PDF-export and display."

Calc

Love this. You know how you have 10.001 and you start incrementing and it goes to 11.001? Hah! Not anymore. "If the starting cell for AutoFill contains a number at the start and the end, and there's no space after the first number, the number at the end is now incremented. This allows easier creation of things like lists of IP addresses (10.0.0.1 is followed by 10.0.0.2)." Note that 10.001 still goes to 11.001 as shown below.

And this is nice. The cell formats come with the numbers when you sort. "When a cell range is sorted using the "Sort Ascending" or "Sort Descending" toolbar button, cell formats are now sorted with the cells."

The info SAYS that you can copy and paste noncontiguous ranges of cells but I can't get it to work. "This feature extends Calc's current copy and paste functionality, to allow copying of a set of multiple non-contiguous ranges. When pasted, all copied data get consolidated into a single range either horizontally or vertically. There are some restrictions when ranges are being copied, in order to ensure that the pasted range become a rectangular range."