Replies

As an active member of the separate OpenOffice.org forum, you may have more chances to find answers to your questions there.

Stephane: that causes the row that I cut to be cut, leaving a row of empty cells, and then replaces the contents of the row above which I want to paste, causing that row to lose its data, rather than shifting everythign down one row.

It seems that you have used the plain “Paste” menu option instead of “Insert cut cells”.

The function RANK(value, range of values) corresponds to your needs, I guess. If two candidates have the same score, they will be ranked equally and the next one will be ranked with a rank +2 (12, 13, 14, 15, 15, 17, 18, …) which I think is a more suitable behaviour.

There should be a file Outlook.pst in this folder:
C:Documents and SettingssquensonLocal SettingsApplication DataMicrosoftOutlook (replace the part in red by your own user name).

This single file contains everything: mails, contacts, calendar, …

In Word, you need to have a section break to define a different page layout. After your last line of the portrait page, use the menu Insert > Break > Section break type “Next page”. Click on this page and use the menu File > Page setup, then on the tab “Margins” select landscape. You can repeat this to go back to a portrait orientation.

For more complex page layout, you need a tool like Scribus.

The bootup password prevents physical, direct access to your computer, like your car key prevents someone to use your car without your consent. On-line hackers are looking for software flaws in order to take control of your computer when it is running, so the use of a bootup password is irrelevant because when your machine is on and running, the viruses or malware residing on your hard disk are automatically activated. The best ways to protect your computer is to use a combination of three software: i) a firewall, ii) an anti-virus, iii) a Malware detection. For each category, there are several products, some of them are even free!

If you don’t want to use the keyboard, you can also right-click the row, then select “Cut”, then right-click the destination row (this row and the ones below will move down) and select “Insert cut cells”.

I have maybe the explanation of this strange behavior.

Excel uses numbers to represent the dates: 1900-01-01 is 1, 1900-01-02 is 2, 2010-01-31 is 40,209. The time is a fraction of a unit, so 2010-01-31 18:00 is internally represented as 40,209.75. If you have in Tools > Options > Calculation the option “1904 system” checked, then Excel considers 1904-01-01 as 1, 2010-01-31 is 38,747.

If you enter a formula like =2/10 and you format the cell as a date, Excel will return a date equals to the number resulting from the formula (in my example, 0.2) and will transform it to a date. If you have in Tools > Options > Calculation the option “1904 system” checked, then Excel will display the date corresponding to 0.2 which is January 1st, 1904, 04:48, or if you have formatted the cell as “m/d”, 1/1.

In such case, the function COUNT(range) is your friend! It returns from the specified range the number of numeric values.

Two other useful functions: COUNTBLANK(range) will count the number of empty cells. COUNTA(range) will count the number of cells with a content.

I think I understand the problem. From your description, you are not using the FullAddress field (e.g. “Wellington Rd. 39”) for the page break, but the field Street which is equal to the first 6 characters of the FullAddress field (in my example “Wellin”). This is a clever way to group on the same page records which most probably belong to the same street but where the address is not properly normalized: “Oxford Street” and “Oxford St.” have the same first six characters (“Oxford”) and will end up on the same page.

I guess in your case there is no simple solution. Address normalization is quite a complex matter in databases. I have dealt in the past with a census database and we had a very sophisticated algorithm to standardize the address and to store each information in separate fields: the house number (which can contain letters or text like “bis” and “ter”!), the street type (“Street”, “Avenue”, …) the street name (“Oxford”, “Wellington”, …). And you will have to deal with ambiguity: an avenue called Saint Paul may be written “St. Paul Avenue” (the street of Mr. Paul Avenue?) or “Saint Paul Av.”, so blindly replacing “St.” by “Street” may give funny results…

I guess the function COUNTIF(range, criteria) will do the trick:
=COUNTIF(A1:A175,”X”)

Note that “X” counts both “X” and “x” cells

Check the format of your destination cell, it seems to be text, so what you type is exactly reproduced as is. Format your cell as general, it should return the right code. BTW, as simpler formula is =RIGHT(“00000” & F9, 5) which can deal with codes in column F of 1 to 5 digits and return a string of 5 characters padded on the left with 0’s.

In your example you wrote: “that appear only in the following cells in this sequence: A2,A3, A5, A6, A8,A9,A11,A12,A14,A15….A150”. What is the logic to not consider the cells A4, A7, A10, A13? Is it because they start with the text “Shift”? In such case, maybe we could consider having a second column like =IF(Left(A4,5)=”Shift”,””,A4), then you can apply the future function on this new column. As it also seems that all the dates are in these cells starting with “Shift”, we eliminate the issue of defining whether a number is part of a date or not. Finally, if a number is always surrounded by an hypen, a blank or a comma, then “-“&SUBSTITUTE(SUBSTITUTE(A4,”,”,”-“),” “,”-“)&”-” will replace all the separators by an hyphen so each now is between two hyphens. So now, with the use of the FIND function which returns an error if a text is not found within another text, and using a function array, the automagic formula is:

Code:

=SUM(IF(ISERR(FIND("-"&E2&"-","-"&SUBSTITUTE(SUBSTITUTE(IF(LEFT($A$1:$A$150,5)="Shift","",$A$1:$A$150),",","-")," ","-")&"-")),0,1))

where E2 is the number to look for. Validate the formula above with Ctrl-Shift-Enter to create an array formula.

EDIT: This formula doesn’t take into account the multiple presence of the same number in one cell…

Hi,

To which connector did you connect your external display?

On your laptop, the VGA connector is an extension of the internal connector for the laptop screen and therefore you cannot use that connector and expect a dual-display. You must use a second connector, like a DVI connector, to connect your screen, then the dual-display should work. A DVI-VGA converter costs a few dollars.

I recommend that you avoid the French Riviera during the period July 14th (French national day) and August 15th (Assumption of Mary). During these four weeks, most of the French, Belgium, Dutch and German people are invading the south of France. Personally, I would come in June (schools are working up to the first days of July), the hotels are not crowded and the days are the longest around the June equinox. On the other hand, the sea will not be as warm as in August. After mid-August, days are shorter and you may end up with more storms.