OpenOffice.org Training, Tips, and Ideas

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.

---

---

How to take a spreadsheet that has firstname, lastname, and initial all in the same column, and turn it into two columns

Sometimes your data isn't as broken down, as granular, as you'd like.

Name

Hanson,John B

Herman,Jill

Jenson,Jim J

What you typically want, for the most power over your data, is this.

Lastname	Firstname
Hanson	John B
Herman	Jill
Jenson	Jim J

Here's how to split it automatically, not manually.

First, you need to take out that space between the comma and the first name. Select all the cells in that name column.

Choose Edit > Find and Replace. Type a comma immediately followed by a space in the Search For field and just type a comma in the Replace With field. Click More and select Current Selection Only.

Then click Replace All.

And you'll see the data looks like this.

 

Now you're ready to split it at the comma. First, be sure that you have an empty column to the right of the Name column, to put the first name and initial into. Then click on the column header to select the whole column.

Choose Data > Text to Columns.

Select the comma, but nothing else. You'll see a preview of how it will be split.

Then click OK and you'll see the actual data split.

How to specify fieldname Not Equal To fieldname in an OpenOffice.org Base query

Let's say that you have a whole bunch of data. You're the head of HR and you have thousands of employees. You want to figure out which ones you need to talk to about new benefits because they've changed their health insurance plan from one year to another. You just need to figure out which are the ones who changed. How do you do that?

You create a query that says "find me all the people for whom Plan08 field does not equal Plan09".

OK, good. But what's the syntax?

Design View

<>  is how you say not equal

and then you just put square brackets around the fieldname.

so in the field for Plan08 you just put   <>[Plan09]

Here's some example data. You need to have the fields set up so they have the value for each employee's choice for health plan for 08, and then again for what they're switching to for 09.

 

The data needs to be in or connected to a database file, an ODB file.

Then you open your database .odb file containing that data, and you click the Queries icon at the side.

You have to create the query in design view or in SQL view, you can't use the wizard.

In design view it's as I showed before, and in SQL it's just "Plan08" <> "Plan09".

Here's how it looks in design view.

 

and here's the results when you click the green-checkmark Run Query icon.

How to generate an email address from firstname and lastname

Let's say that you work at one of those companies that has a nice leftover-from-the-8-character-file-name-limit-days email format. Solveig Haugland becomes shauglan, Bob Nelson becomes bnelson, and so on.

And let's also say, as long as we're dreaming up situations, that you've got a spreadsheet full of first and last names but you don't have their emails.

You could type in all the email addresses but even an intern who's paid well by the hour is going to balk at that.

The faster way is to use CONCATENATE and LEFT.

You've got this.

Firstname	Lastname	Email
Shelly	Nelson
Bill	Mizrahi
Steve	Santos

You want this.

Firstname	Lastname	Email
Shelly	Nelson	[email protected]
Bill	Mizrahi	[email protected]
Steve	Santos	[email protected]

Here's how to get it.

Syntax

=CONCATENATE(LEFT(cell_with_firstname;1);LEFT(cell_with_lastname;7);"@yourdomain")

Example

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

This is a very useful feature, I think, so I'm reposting it.

Anytime you can help people 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 some will 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]

---

Sharing Spreadsheets in OpenOffice.org 3.0

You can do it!

Here's what you need to do.

Choose Tools > Share Document.

In the window that appears, select the checkbox. Also note the highlighted portion of the text.

Click OK.

You'll see "sharing" in the title bar.

Repeat the previous steps to unshare. You'll see this message.

I don't have a network right now to test and get screen shots on but I tested it during a previous class with a network. Essentially updates of other people's tasks are displayed when you save. Sometimes before. And sometimes there is a lock from another person but only temporarily.

Updated the 10th: An Impress PDF presentation on OpenOffice.org 3.0 features

I created this presentation  for a client, about the 3.0 features in OpenOffice.org. I'm going to do a more detailed article but I thought I would post this since I've done it. Here's the solver.ods spreadsheet you can use to fiddle with.

I got pretty excited about some of them, especially the 3-up layout with lines already in there, and the far easier Impress handout printing.

And for those who like a good cross-reference, you don't need to create them first; you can just point to a heading in a list and select it, to make the cross-reference.

Also very exciting is the PDF editing, which does some very Adobe Acrobat type things. It's in an extension you can get here.

In the words of Douglas Adams, share and enjoy!

Sorting mixed 5- and 9-digit zip codes in OpenOffice.org Calc or Microsoft Excel

Every advance leads to benefits and to problems.

The 9-digit zip code is great for delivery accuracy. But they aren't required. So you have mixed 5-digits and 9-digits.

Now just try sorting your address info by zip code when there's mixed 5s and 9s. Here's what you get when you sort a standard address list by zip code.  It's like cattle and sheep, they don't mix.

What do you do?

There are three things you can do:
1 - Enforce a 9-digit zip. Everyone without four digits gets -0000 whether they want it or not.
2 - Put a ' in front of every zip code. It doesn't print but it forces the zip code to think of itself as text. (This also helps with not losing leading zeroes.)
3 - Split the column into two, so you have the zip in one column and the four-digit extension in the other. This is kind of like the forcing-9-digit solution.

The explanations follow but if you want to root around in an example spreadsheet, here's a spreadsheet with options 2 and 3.

Solution 1
Solution 1 is self-explanatory.

Solution 2
Just type a '   a regular apostrophe on the keyboard, to the left of the first character of every zip code. It doesn't show but it forces text format.

See? The ' is there in the entry field but it doesn't show in the spreadsheet cell.

Typing ' into thousands of cells takes a while. So you can search and replace. There might be a better way to do this but this at least doesn't suck.

Click in the Zip Code heading in your spreadsheet, then  choose Edit > Find and Replace. Click More Options and fill out the window as shown, searching for ^0, caret zero, and replacing with '0, apostrophe zero.

You'll need to do this once for 0, then for 1, then for 2, and so on. (I've tried to figure out a faster way, plus submit any suggestions.) 

Either replace one at a time if you're cautious, or go nuts and replace all. You might want to select the whole column of zip codes, too, and select the Current Selection Only checkbox.

When you're done, and when you sort that data, the zip codes sort correctly.

Solution 3

You can split your data into two cells with the LEFT and RIGHT functions. I'm throwing in IF too because sometimes you'll want the right-hand four digits (the extension) and sometimes you'll want 0000. (Or just leave it blank, whatever you want to do.)

This is what I want to achieve.

And this is how I get it. The formula for the first column, where I extract the first five digits, is simple.

The right-hand side is a little more complicated since you're dealing with variable-length zip codes. But basically you're saying if the zip code is just five digits, then create a new 4-digit extension, "-0000" (or just "0000" depending on how you want to deal with the dash). And then if it isn't just five digits, then you want to see the right-hand five digits of the zip code (including the dash) or the right-hand four digits (if you want to leave out the dash and put it in manually somehow).

Then you just drag down those formulas to all the zip code cells.

If you want to turn those columns into normal text, just copy them, choose Edit > Paste Special, choose to NOT paste formulas, and click OK.

The pasted results are nothing but numbers.

Now when you sort, you just need to be sure to do it by two levels, first by the main zip code, then by the extension. BE SURE that you set the Ascending or Descending the same for both.

And you get your results, sorted correctly.

Here's a spreadsheet with options 2 and 3.

Cell formats, and using NOW() and other functions in OpenOffice Calc spreadsheets that you use in databases and mail merges

Mail merges work fine with spreadsheets as the data source. You create a spreadsheet of data, then choose File > New > Database, specify connecting to an existing database, Spreadsheet as the type, then click Next and point to the database itself.

Here are a couple things about formats, though.

Formats don't come through. So if you want your Salary field to have dollar signs, decimals, etc. you need to set the format. Likewise with date or time.

One way to do this is just set the formats when you view them. Press F4 and expand to view the query or table you want. Then just right-click on the column heading and choose Column Format.

Then in the window that appears you can select a category (Currency, Date, Number) and the the specific format you want. Click OK.

You can do the same thing in the database file itself. Open the .odb file, click the Tables or the Queries icon at the left, then double-click on the particular table (the sheet or query).

In the window that appears, right-click on the column heading, choose Column Format as before...

and in the window that appears, as before, make the choice you want. Click OK.

Now, here's a related topic. Can you use the =NOW() function in a spreadsheet and have it interpreted correctly in the database and in mail merges? (Thanks to a Colorado Springs LUG member for this idea.) The answer is Yes.

Here's a spreadsheet with =NOW() showing the absolutely current time and date.

Here's what it looks like, with nothing done to it, in the database view. It needs some tender loving formatting since it's just showing the internal numeric value. Right-click on the column heading and choose Column Format.

 

Set the format you want. The NOW() function can let you use a date or time format since it contains both.

 

And now it looks fine in the database view.

 

When I use this field in a mail merge....

Here's the output when I format the field as a date:

 

and when I format it as a time.

How to create a series, or just repeat a cell, in OpenOffice Calc spreadsheets

Never retype when you can do it quicker!

In OpenOffice.org Calc spreadsheets, as in Excel, you can click on a cell, find the little black handle in the lower right corner, and drag it up, left, right, or down, to get additional content.

 

If it's a number when you drag you'll increment by 1. If it's a value like the days of the week or names of months, it will increment as in January February March.

 

If it's a formula where you've just typed it normally without absolute  references, then drag it down through a column or row, it will repeat the formula as shown. Here's the first cell with the formula:

  

Drag down and you get similar formulas referencing the next-door cell, not the original cell.

If it's anything else it will just repeat.

If it's a number or day/week/month (not a formula) that usually increments, and you just want it to repeat, you can:

- Select the cell containing the number, as well as all the cells that you want it to repeat into, and choose Edit > Fill > down or right or whatever fits the cells you've selected

- Or you can hold down Ctrl when you drag and it'll do the same thing: repeat instead of incrementing. This works for incrementing months/days etc and numbers.

Note: for formulas, if you want them to not adjust as you drag, you use absolute references as in Excel. $A$4 instead of A4 for instance.

Data Pilot (pivot table) tutorial for OpenOffice Calc

This PDF is from my Calc workbooks, and covers Data Pilots, Calc's version of pivot tables.

You'll also need this lab file.

See also this post.

Creating templates, and documents based on templates

This is a "classic" post but important and useful. See also info on styles .

Let's say you have the following:

• A bunch of styles you need to have when you create a document.
• Some canned text, like your signature or a graphic logo, that you use in a lot of documents
• Or you just have to make sure that all your white papers or customer contracts or brochures follow certain formatting standards
  

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

Making a Template

Open a new document.

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

Then choose File > Templates > Save.

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

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

Type the name of your template like Meeting Minutes.

Click OK.

Now you've got a template.

Using a Template

You need to create a new document using that template.

Choose File > New > Templates and Documents.

Select the Template icon on the left.

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

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

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

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

Choose File > Templates > Organize.

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

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

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

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

Pointing to an Existing Directory of Templates

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

Converting, If You Want to Point to Microsoft Format Templates
First, if they're in Microsoft format, you need to convert them to OpenOffice format. Choose File > Wizards > Document Converter. Follow the wizard through to create a converted set of copies of the templates.

Step 1.

Step 2, then just follow the wizard as prompted.

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

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

Click OK, and OK, and OK  again.

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

---

Checking for broken links when you're using Edit > Paste Special, Link, in OpenOffice

Paste Special is a great feature. Copy something, then go to a different spreadsheet sheet, or a different OpenOffice.org, and choose Edit > Paste Special. (Or Ctrl Shift V.) You get these options.

If you're pasting a spreadsheet to a spreadsheet

If you're pasting a spreadsheet to a text doc

If you're pasting a text doc to a text doc, or a text doc to a spreadsheet

 

If you select the Link option in spreadsheet-to-spreadsheet, or the DDE Link option when pasting any other time, you get a connection between the pasted from doc and the pasted into doc. When what you originally copied changes, then the pasted version of it changes, too.

This continues to work unless somebody deletes, renames, or otherwise messes with the original file you copied.

Unfortunately, if this happens, there's no big red flag waving to tell you. The data is still there; the link is just lost.

Here's how to check to see if the link is still working. Choose Edit > Links.

If you see links listed there, they're working and nobody has messed up the name or location of the original file.

If the the Links option is dimmed, or if a link to the file you're checking on isn't listed, then the link is broken and you need to do Paste Special, Link again to make the pasted data update when the original data does.

The word completion-like feature in Openoffice.org Calc

I'm a big non-fan of word completion.

But when you go under Tools > AutoCorrect in Calc to turn off word completion in Calc, as you would in Writer, there's no Word Completion tab.

You need to choose Tools > Cell Contents > Autoinput to turn it off, or turn it on again later.

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

If you use Linux, you MUST try gLabels

Download it now, use it now.

http://glabels.sourceforge.net/

This is a beautiful program, a well-balanced combination of power, simplicity, good design, and ease of use. Thanks to Keith for pointing it out to me.

There's no Windows or Mac version, sadly.

Among the things you can do are:
- automatically (no effort on your party) suppress empty address lines
- do bar codes
- point straight to a CSV or similar format file to bring in records
- deselect records you don't want to print
- add graphics and drawing shapes
- apply formatting
- easily preview the whole sheet

Here's a screen shot with a summary of what you do. I'll do more detailed instructions later but here's the quick info. I love it.

 

OpenOffice Calc spreadsheet settings for how a cell is filled

When it comes to cells, I like my content to look either like the first cell, or the second cell. If there's enough room for all the content; great. If there isn't, I like a nice wrap.

To wrap content in a cell, select the cell or cells then choose Format > Cells, Alignment tab, and select Wrap Text Automatically.

If you've got just barely not enough room, you can also move your mouse over the right-hand border of the cell's COLUMN separator, and when it looks like this, double-click. Bam, the whole column will be just barely as wide as the widest cell in the column.

Here are a couple other options that I just started looking at. Shrink to Fit Cell Size, and for the horizontal justification, Filled. You get to them the same way you get to the wrap option: select the cell or cells then choose Format > Cells, Alignment tab, and select the options you want.

Here's how each option looks. The demo content is in plain text and the explanation is in caps to the right of the relevant cell. Frankly I'm not sure how much I would use the Filled justification; seems a little dangerous.

---

---

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

I've been blogging about sorting for a while:

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

There's just one more thing to mention: creating your own custom sort order.

Existing sort orders are things like Monday Tuesday Wednesday (the right order, which is not alphabetical). But let's say you've got things you want in a certain logical but non-alphabetical order that aren't already set up in OpenOffice.org: titles of books or people, procedures done in a certain order, or your own abbreviations for the days of the week. You can create sort orders for those very easily so you can sort by them.

Let's say you've got this data.

This is the right order. If you had them out of order, though, and wanted to sort them, all you'd have the option for is alphabetic. Which isn't right. So you create an order to sort them by.

Select just the content of the sort order, nothing else. Be sure it's in the order you want it.

Choose Tools > Options > OpenOffice.org  Calc > Sort Lists.

Now just click Copy.

The list will appear. Click OK.

Now just sort the way you would with a custom sort order. You'll see your new sort order in the sort window.

Sort orders are nice for quick data entry, too. If you type the first word for a sort order, then find the small black handle in the lower right corner of the cell and drag, you'll be able to easily fill the cells with the rest of the content in the sort order.

Type the first word

Find the black handle and drag (magnified to show the handle)

The data will fill in each cell you drag through, in the order set up in the sort list.

Making changes to an OpenOffice.org chart in Draw

Thanks to Brian from Howard County Library for pointing this out to me.

Let's say you've got a chart, and it's fine, but you're just not that jazzed about how it looks. You'd like a more Graphic Designer look to it. Here's how to break it down and do whatever you want to it.

You've got this data; great new data on the number of users that use open source computers at the library branches.

And you make a basic bar chart, or whatever kind of chart.

And you can do some things. Double-click the chart, click on the element you want to delete, and press Delete.

You can also right-click on things like the background, choose Object Properties, and choose Invisible instead of a line and click OK. 

Then the lines don't show. (You could also re-create the chart without the lines.)

Another option, for adding text boxes, is choose View > Toolbars > Drawing. Click the T text box tool and draw a text box near the chart and type immediately. Then drag the text box over the chart.

But here's the thing you can do in Draw. Copy the chart; just click on it once and copy it. Then choose File > New > Drawing and paste it into the main slide work area.

Right-click on the chart and choose Break.

And now you can click on any particular part and just delete it or reformat it.

 

You could get to something like this.

TechTarget Article: Using Master Documents in OpenOffice Writer

I've written an article for TechTarget.com about using master documents in OpenOffice.org Writer.

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

Master documents are used to combine lots of other Writer documents. They're similar to Word master files or Frame book files in that they organize your subdocuments, let you create a unified table of contents, etc.  They're a bit picky but once you've got them set up, they work quite reliably.

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

---

---

Getting the contents of a table or query into an OpenOffice.org Calc spreadsheet

For the longest time, the really easy way of bringing database content into a document didn't work in Calc spreadsheets. At least, not for me. But now in 2.4 it does. So here you go. It's the same approach you use in Writer, just a little more limited.

Choose View > Data sources or  press F4.

Expand the DB you want, then the table or query you want. Select the table or query name.

Click on the upper left corner as shown, the un-obvious little gray square.
 

Click and hold down and drag into the document. And you'll get your data.

---

Copying only the unique values from a set of cells in OpenOffice.org Calc spreadsheets

So there you are. You've got a whole bunch of people signed up for various events you're planning. Or they've ordered a bunch of your products. Some have signed up for more than one event; some have ordered more than one product.  You want to just get a list of the customers who've signed up. If a customer has signed up for three events, you just want her name to appear once. You just want the unique values.

One thing you can do is use the Standard Filter.

Select the column of data, either with the heading or without.

Choose Data > Filter > Standard Filter.

Select None as the Field Name then click More.

• If you didn't select a column heading like Customer, deselect Range Contains Column Labels.
• Select No Duplication
• Select Copy Results To, click in the empty field, then either type the sheet and cell where you want it copied, or click in the cell in your spreadsheet where you want it copied.

Click OK.

You'll get only the unique values from that cell range, pasted starting at the cell you specified.

---

---

Sorting data in OpenOffice.org Calc (repost)

You want to do some good, hard sorting. Not just sorting by the first column, but by perhaps the third column. Maybe you want to sort first by state, then by city, then by last name. For that, you need the Sort window under Data > Sort.

You have your data.  Select all the data to sort, and either select the headings or not. You're good either way.

Choose Data > Sort.

Click the Options tab. You need to tell Calc whether you've got headings selected or not. If you've got headings selected, you want the option shown, "Range Contains Column Labels," to be selected. If you didn't, unmark it.

Now click the Sort Criteria tab. If you selected headings you'll see the headings themselves; if you didn't then you'll just see Column A, etc. Select the column to sort by, and Ascending or Descending.

Click OK.

You get your results. Here, the amounts under the column for the year 2000 are sorted in ascending order.

Now, let's look at a different set of data. You have a lot of people from the same state, and several cities per state. In this case you might want to just sort by last name, but you could also group by location. So you'd sort by state (the broadest category), then city, then alphabetically by last name. Click the image to see more detail.

Select the data, with or without headings, and choose Data > Sort. In the Options tab, be sure to select the Range Contains Column Labels option if you selected headings.

In the Sort Criteria tab, select first State, then City, then Last Name.

Click OK.

You get your results. Here's a closeup of one section, followed by the complete data. Click either to see them closeup.

This window that I've covered in this blog entry is pretty much what you need. If you want to go a little farther and sort by something else, like days of the week in the order they come, not alphabetical order, tune in for the next sorting blog.

---

---

Un-displaying the grid lines in OpenOffice.org Calc

Tired of those pesky gridlines being displayed onscreen? Here's how to turn them off.

Thanks to Marek for pointing this out -- I like to consider myself the queen of "it's under tools > options" but I only looked under the View menu before resorting to hack measures.

Choose Tools > Options > Calc, View. Deselect Grid Lines. You can change the color of the grid lines here, too.

Note that by default, they don't print. To make them print, choose Format > Page, Sheet tab.

---

---

Using Names to have a more readable reference to items in formulas

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.

---

---

Inserting more than one row at a time in OpenOffice.org Calc

Inserting a row in OpenOffice.org Calc is easy. Let's say you've made this schedule and you need another row  because you need more slots for night life.

Right-click on the row below where you want the new row to appear, and choose Insert Rows.

Bam, you get a new row. Now you have two blank ones so if you decide you need even more new rows, you can select two rows, choose Insert Rows again....

and then you get two more new blank rows.

That's the way I've always done it.

But then I had a brainwave -- you don't have to select blank rows in order to insert rows. That is, if you want to insert three new rows, just select three rows, period, whether they have content in them or not. Then as usual right-click on the row headings and choose Insert Rows.

and you get your three new rows, above the rows that you selected.

I'm not sure how I got stuck on the idea that you have to select blank rows if you want to insert multiple rows at a time. But you don't. So here's the summary.

If you want to insert multiple rows, just select that number of rows, right-click on the row headings, and choose Insert Rows. Doesn't matter if there's content in the rows that you select;  the content won't be affected by the Insert Rows action.

---

---

Any way you want it: OpenOffice.org Calc spreadsheet keyboard shortcuts

I get a lot of requests from people switching office suites who want to know how a feature is done in Microsoft Office, compared to how it works in OpenOffice.org.

It usually ends up that the person wants a list of shortcuts. And I say "but you can have any shortcut you want in OpenOffice.org, it's incredibly configurable." I don't really like to give out lists like that because it gives the impression that it's fixed. If someone wants Ctrl 2 to make text double-spaced and they see it's assigned to the Heading2 paragraph style, they might think there's nothing they can do about it.

All this is kind of a meandering introduction to the following general topics:

You can have virtually any shortcuts you want in OpenOffice.org.

There are a zillion  features you don't know about that you can apply shortcuts to.

It's really easy to do. And once you've got it set to your preferences, you can easily give the file to your friends.

---

You can have virtually any shortcuts you want in OpenOffice.org.
Choose Tools > Customize, and click the Keyboard tab. Those are the current shortcuts. But to change one, just select something in the Category list, something in the Function list, the Keyboard Shortcut of your choice, and click Modify. Bam, you have your new shortcut.  The world is your oyster.

There are a zillion  features you don't know about that you can apply shortcuts to.
Sooooo many features that aren't on the menus or toolbars, and possibly not on the right-click menus, are in the Keyboard tab.

As mentioned, choose Tools > Customize, Keyboard tab.

Select the first Category, and scroll through the Functions. Look at all that. You can set up any of those to use a keyboard shortcut you want, or you might see something you like that has a keyboard shortcut with it already.

The keyboard shortcuts are different for OpenOffice.org in general, and for Writer, for Calc, for Impress, and for Draw.

Here are just a few functions for Calc. The first screen shot shows that it's Ctrl Page Down to go to the next sheet, and Ctrl Page Up to go to the previous sheet. You can use this as is or change it.

Here are just a few functions for Writer

Here are just a few functions for Draw

It's really easy to do. And once you've got it set to your preferences, you can easily give the file to your friends.

All right. You've chosen Tools > Customize. You've clicked the Keyboard tab. You've scrolled through and found the feature for making line spacing doubled. And in the Shortcut Keys list you find Ctrl 2, the shortcut you want to use. It's already occupied by being assigned to Heading 2. That's fine. Just click Modify and it'll switch.



Here's what it looks like now, with Ctrl 2 assigned to double-spacing.



Now let's say you want to share this and any other shortcut configurations you've made with your friends, or all the folks you support in your job as an IT professional.

* Click Save.
* Name the file and click Save.

Then give the file to someone else and tell them to:

* Choose Tools > Customize, Keyboard tab.
* Click Load
* Find the file and click Open

Want to give this post a plug on FSDaily?

---

---

Different page layouts for sheets in the same spreadsheet, in OpenOffice.org Calc

Let's talk spreadsheet page layout.

Doing it simply is simple enough. Choose Format > Page, and make your changes. Margins and orientation in the Page tab; Headers in the Header tab; Borders around the content in the Border tab; etc.

But....what if you've got content in a couple different sheets of your spreadsheet, or more. Let's say that the data in the first sheet is pretty wide so you'd like a landscape layout, and the data in the second sheet is narrow and a different type of information, so you'd like it not just portrait but with a different header. You want different page layout in each sheet of your spreadsheet.

Well, the way I just showed you won't work because the changes apply to the entire spreadsheet.

The reason that the changes apply to the entire spreadsheet is because the Default page style is applied to every sheet in the spreadsheet. (A page style is just a group of formatting attributes associated with a style name.) And when you choose Format > Page, even though you might not be thinking about styles at all, you're modifying the Default page style.

How to get around this? Well, you just need to have one page style on the first sheet, one page style on the second sheet, and so on.

That way when  you modify one page style, you're not affecting the other sheets because the page style is only applied to that one sheet.

All right. Enough description. Here's exactly how to do it.

Step 1: Create page styles

Step 2: Apply a different page style to each sheet

Just so you see the sample, here's my spreadsheet. The first tab has a few pages of sales data; the second sheet, with different and less data, is more of a summary set of data.

Step 1: Create page styles

Choose Format > Styles and Formatting. Click the Page Styles icon at the top.

Right-click in the window and choose New.

Name the style.

Set attributes for the style.

Click OK. The style appears in the Styles and Formatting window.

Now make another style, the same way. Name the style.

Set attributes. (Here's how you'd change content in the header.)

Click OK. The style will appear in the Styles and Formatting window.

Step 2: Apply a different page style to each sheet

This is pretty simple. Click on the tab for the first sheet, and in the Styles and Formatting window, double-click the page style you want to apply.

 

Then click on the tab for the next sheet, and double-click the style for that sheet. If you want to have one style for one sheet, and another style for three other sheets, just keep going, applying the appropriate style each time.

Now you can see in Page Preview that the layouts are different. Choose File > Page Preview.

Here's the layout for one of the pages in sheet1.

And here's a page in sheet2. (I zoomed in a little to show the perhaps-not-that-necessary header but trust me, it's portrait rather than landscape like the other sheet.)

---

---

A nice little Calc spreadsheet function: SUMIF (works in Excel too)

I came across this while working with a spreadsheet that was perhaps a little too fancy to come over from Excel to OpenOffice Calc.

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

business expense; donations; mortgage interest
or
toys; paints and crayons; DVDs; nap supplies
or
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.

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

---

---

Good information on the OpenOffice.org documentation wiki about regular expressions in Calc and Writer

Calc 
http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Regular_Expressions_in_Calc

Writer
http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Regular_Expressions_in_Writer

---

---

How to do regular expressions in OpenOffice.org Calc functions

I talked in this post about how to use regular expressions in filters.  The key point is that where you would use * in Excel, you use .* in Calc.

Filters aren't the only place where regular expressions come up.  Let's say you want to count the number of people whose last name ends in "son" in a big list of names, range C5:C300. You can use COUNTIF. Here's an example. First you have the range, then the text you want to find in the range. The result is the number of time that text was found in the range.

I hit Return and I get the correct result, 2. (Only 2 because I'm lazy and didn't create 300+ sample names for this blog. ;>  )

Now, the reason that the correct result is shown is that I have this option marked under Tools > Options > OpenOffice.org Calc > Calculate. It's pretty straightforward: "Enable Regular Expressions in Formulas."

If I unmark  that option and click OK, then I get 0 as my result. So if you do regular expressions in formulas, keep it marked.

Note: I've also been told that you should deselect Search Criteria = and <> Must Apply to the Whole Cell but I haven't noticed an effect one way or the other.

Here's some additional guidance for using regular expressions.

Info on the OpenOffice.org wiki

Syntax examples for COUNTIF(), counting cells that meet the specified criteria in the range B2:B35. Use the same symbols for other functions that allow regular expressions.

Note: There aren't any handy dropdown lists in Filter windows so that you can select "Contains" or "Starts with". You need to type out the syntax. However, the following table should help.

 

What you want	What to type in the cell where the count should appear
Count the number of cells that contain only the word Linux	=COUNTIF(B2:B35;"Linux")
Count the number of cells that begin with Linux	=COUNTIF(B2:B35;"Linux.*")
Count the number of cells that are not equal to Linux	=COUNTIF(B2:B35;"<>Linux")
Count the number of cells that do not begin with Linux	=COUNTIF(B2:B35;"<>^Linux.*")
Count the number of cells that do not end with Linux	=COUNTIF(B2:B35;"<>.*Linux$")
Contains Linux	=COUNTIF(B2:B35;".*Linux.*")
Does not contain Linux	=COUNTIF(B2:B35;"<>.*Linux.*")

 

---

---

How to quickly get the cursor down and to the beginning of the next row, in OpenOffice Calc

I posted a blog here about how to modify OpenOffice.org Calc to make the Delete key just delete, and another key like the Backspace key bring up that little delete window.

Here's another little modification. A lot of folks who use Excel are used to pressing Enter or Return to go down one row and all the way to the beginning of the row. If you're on E4 and press Enter, then you are suddenly at F1.

When you press Enter in Calc, however, you just go down one, like from E4 to F4. You can go under Tools > Options > Calc > General and change what the Enter key does, but you can't change it there to doing it the Excel Way.

However, to go to the first cell of the next row almost as quickly in Calc as in Excel, you can press Enter, then Home. On my keyboard they're pretty close together. Enter takes you down one cell, and Home takes you all the way to the left of that row.

---

---

OpenOffice Calc and Writer Chart Users -- This Is for You

This looks pretty fantastic. New features for charting coming in OpenOffice.org 2.4.

http://blogs.sun.com/GullFOSS/entry/new_chart_features_in_openoffice

and more here: http://wiki.services.openoffice.org/wiki/Chart2/Features2.4

To try it out, click here for the announcement and download info.

Here's just one of the new features, quoting from this post.

Equation and Value of R² for Trend Lines

Now, it is possible to display the equation for a trend line (regression curve) next to it. The formula object can be moved around, formatted with a number format, font and graphical formatting.

Alternatively, or in addition, the correlation coefficient R² can be displayed in the formula object.

---

---

Going to a new line in the same OpenOffice Calc cell

In Excel and in Calc, you can create a carriage return, aka line break, to go to a new line within the same cell.

You do this by pressing Ctrl Enter to go to a new line.

However. In Excel you can do this with your cursor in the input field at the top or in the cell. In Calc, your cursor needs to be in the cell.

Also (thanks to a reader for a note on this). One occasional issue. This applies rarely and only if you've got some serious spreadsheet action. But keep in mind that the carriage return will sometimes be interpreted as a space. Do you care? Probably not if you're not running any evaluations of that cell. But keep this bug in mind.

---

---

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.

---

---

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

I could have sworn I had posted this already, but I think it was just included in a comment on this post.

http://openoffice.blogs.com/openoffice/2007/11/using-the-autof.html

Thanks to Huw for the tips. For information on the sites, see:
http://www.openoffice.org/issues/show_bug.cgi?id=33851
http://wiki.services.openoffice.org/wiki/Calc/Drafts/Issue_33851
http://wiki.services.openoffice.org/wiki/Talk:Calc/Drafts/Issue_33851

 

Let's say you've got a big list of employees: name, address, etc. When you apply the filter and view, for instance, only people from Montana, some of the data isn’t shown. If you then copy, paste, delete, or perform other operations on the data, what happens to the data that isn’t shown? If you delete Artie Anderson from Montana and Cindy Chalmers from Montana, what happens to Betsy Bates from Nevada?

It all depends on the operation. Some, like delete, leave the unshown data alone. Some do affect the unshown data.

Operations that DO affect filtered out rows.

• Cut and Paste

• Move (dragging)

• Fille (Edit > Fill or dragging)

Operations which do NOT affect filtered out rows:

• Copy

• Delete contents

• Delete row

• Format

• Find & Replace in current selection

More About Operations that DO Affect Filtered-Out Rows

Cut and Paste, Versus Copy and Paste

With Cut and Paste, the non-shown data is cut and also pasted.

• When you paste the data outside of the filter range, the whole set of data is pasted and unaffected anymore by the filter.

• When you paste the data inside the filter range, the whole set of data is pasted, and all data is shown at first, even data that shouldn’t show for the current filter selections. However, if you re-apply the filter selections using the dropdown lists in the heading row, then the data is filtered correctly.

Click the following to see a bigger image.

With Copy and Paste, the behavior is different. If you cut, you get the nonshown rows, but if  you copy, you don’t.

Dragging Cells to Move Them

When you move rows (dragging) that include unshown rows, the behavior is the same as cut and paste. The unshown rows between shown rows are moved along with the shown rows.

Fill (Edit > Fill or Dragging the Cell Handle)

Here’s what happens with Fill. Here’s some sample data, and currently everyone is in the same department.

You now look at only people from Colorado.

You change the department for the first person from Colorado, and drag that department down through all the other people from Colorado.

Now, all the nonshown rows after the first row you changed are affected, but not the rows before that.

More About Operations that Don’t Affect Filtered-Out Rows

Copying and pasting, deleting, formatting, and Find and Replace don’t affect unshown data. The following section provides an example.

Deleting

Here’s a walkthrough of deleting rows while the filter is on. Rows that aren’t shown aren’t affected.

Look at the range from row 15, Dan Montbatten, to row 20, Beth Jerlin. Dan and Beth are both from Montana.  In between are Jon, Marcus, and Kyle.

The next illustration shows an AutoFilter with only people from Montana, which includes Dan and Beth but excludes the three rows between.

 

Now delete Dan and Beth.

And they go away. However, when the state autofilter criterion is removed, Jon, Marcus, and Kyle are still there.

 

 

---

---