WFTL-LUG Members

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.

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.

Excellent article on that, here:

http://www.heraldscotland.com

Well said. Articulate and right.

You can now insert notes in Drawings and Presentations, as you can in Writer. Just choose Insert > Comment.

See here [link].

So, liberal atheists who do not sleep around are intelligent.

Well, what can I say…

In all seriousness, the only surprie to me is the relatively small IQ gap. I would have thought it would be higher. Particularly because atheism is involved, where surely it cannot be very intelligent to believe in imaginary deities. Perhaps this small gap is due to this being a US study: the US has a large pro-religion and anti-atheism bias.

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.

Read this article from a Nigerian newspaper. The use of language is quite poetic. And I love the “nobody saw it coming - not even seers”.

I used to work in Nigeria in the early 80s and I remember being struck by this then. The pictures and article remind me of how it was and show me that apparently not much has changed.

Africa is quite beautiful and I must go back.

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.

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.

I'm going to be starting a regularly scheduled series of seminars soon, some paid and some free. I'd like your ideas for what would be the topics you'd like me to teach, as well as times (day/evening, week/weekend) and preferred length. Thanks for your help!

E-mail Address: *What topics would you like to learn about in an online seminar?When would you like online seminars (day/weekend)Weekdays
WeekendsWhen would you like online seminars (time of day)6-9 AM
9-12 AM
12-3 PM
3-6 PM
6-9 PMOther comments * RequiredCreate Email Forms

I'm going to be starting a regularly scheduled series of seminars soon, some paid and some free. I'd like your ideas for what would be the topics you'd like me to teach, as well as times (day/evening, week/weekend) and preferred length. Thanks for your help!

E-mail Address: *What topics would you like to learn about in an online seminar?When would you like online seminars (day/weekend)Weekdays
WeekendsWhen would you like online seminars (time of day)6-9 AM
9-12 AM
12-3 PM
3-6 PM
6-9 PMOther comments * RequiredCreate Email Forms

Ever wanted to just be guided through how to do a basic mail merge? Here's your chance! I'm going to be testing out my new online seminar tool, and I'd like your help. I'll be hosting a 2-3 hour seminar on how to do mail merges, using a spreadsheet for your data source.

• Creating the data in the proper layout
• Creating a new database based on the spreadsheet
• Creating a letter using fields from the spreadsheet
• Creating labels using fields from the spreadsheet
• Sorting and selecting the records you want to print
• If time permits: envelopes

DimDim is the tool I'm using, and it purports to support Windows, Mac, and Linux.

I'll provide a short downloadable guide to the mail merge process, as well, during the seminar as a reference.

Click on the widget to register. I can have up to 20 participants, and it's first come first served. Please register only if you can attend. It's 10 AM mountain time on Thursday February 25th.

When you register you'll receive an email with the call-in number and URL. You'll need to call in from a phone in a quiet location. The phone call is not free but should be relatively low-cost.

Thanks for helping me out with this test, those who register, and I look forward to clarifying the mail merge process.

Ever wanted to just be guided through how to do a basic mail merge? Here's your chance! I'm going to be testing out my new online seminar tool, and I'd like your help. I'll be hosting a 2-3 hour seminar on how to do mail merges, using a spreadsheet for your data source.

• Creating the data in the proper layout
• Creating a new database based on the spreadsheet
• Creating a letter using fields from the spreadsheet
• Creating labels using fields from the spreadsheet
• Sorting and selecting the records you want to print
• If time permits: envelopes

DimDim is the tool I'm using, and it purports to support Windows, Mac, and Linux.

I'll provide a short downloadable guide to the mail merge process, as well, during the seminar as a reference.

Click on the widget to register. I can have up to 20 participants, and it's first come first served. Please register only if you can attend. It's 10 AM mountain time on Thursday February 25th.

When you register you'll receive an email with the call-in number and URL. You'll need to call in from a phone in a quiet location. The phone call is not free but should be relatively low-cost.

Thanks for helping me out with this test, those who register, and I look forward to clarifying the mail merge process.

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

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

Tonight, I feel energized by watching Bill Maher on HBO, saying the things I think. He is funny, irreverent, honest and intelligent.And he gets away with saying things 100 times more bluntly than I ever will!

I feel that with Christopher Hitchens and Bill Naher, I am a voice in the wilderness in not liking the nationalistic Olympic hysteria that surrounds me.

Hey, I like fun too, and watching skiing long jump competitions is exciting, but when I see all these people carrying flags I think “Nuremberg”. Nationalism, of which the Olympics is a clear and excessive example, is a force for evil. Nationalism is the excuse that allowed Mao to kill millions of Chinese. It is the force that enabled Nazism. It causes wars: it has killed as many people as religion.

So to celebrate nationalism, which seems to me the only point of the Olympics, is wrong. Our newspapers’ front pages are all about “Gold!”. Sad.

Now if we celebrated athletic achievement without regard to the athletes’ passports, that would be great.

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

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

With the paintbrush or pen tool, you can draw a straight line, at any angle (not just 45/90/180) by clicking where you want the line to start, holding down Shift, then clicking where you want it to end.