OpenOffice.org Training, Tips, and Ideas

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.