View previous topic :: View next topic |
Author |
Message |
valterb The Anomaly (earlier version)
Joined: Sep 23, 2005 Posts: 463 Location: San Giuliano Terme, Pisa, Italy
|
Posted: Wed Jun 09, 2010 8:39 am Post subject: Calc automatic filling and lists |
|
Hi again folks. I have a quite long introduction before I explain what is, in my opinion, a bug.
I'm working for a running race in my town and I'm the person in charge collecting all people entries on the day of the race.
In Calc I have a list of athletes that would take part of the race; athletes are listed with their Names (cell A#), Clubs (cell B#), Birthdate (C#) and Categories (D#) in a row. All these informations are stored in Sheet1.
In Sheet2 in cell A# I have the number that the athlet will have on his shirt, than I write just the name of the person (in cell B#) and automatically I have other informations filled in the other cell of the same row. At the end of my entries I will have a list of athletes that will attend our race with their number. I obtain it filling the cell C # with this formula
=INDIRECT("Sheet1.B" & MATCH(B2;Sheet1.$A$1:$A$960))
(hope my translation of commands in the formula are correct).
All this works very well until I save my file, close it and reopen.
When I reopen the file, when I fill the name-cell nothing happens. But if I go in the cell where I want that the name of the club is displayed, put the cursor at the end of the formula, type a space and than ENTER the name of the club appears magically.
I'm going to attach my file to this post so you can have a look at what really happens.
The file works correctly with OpenOffice.org 3.2. |
|
Back to top |
|
|
narf The Anomaly
Joined: Jan 21, 2007 Posts: 1075
|
Posted: Wed Jun 09, 2010 10:23 am Post subject: |
|
I can also reproduce this behavior in my Go-oo installation but not in OpenOffice.org 3.1.1. That means that one or more of the OpenOffice.org code modications made by Novell's Go-oo engineers is causing this bug.
I have opened bug 3608 to track this issue.
Fran |
|
Back to top |
|
|
pluby The Architect
Joined: Jun 16, 2003 Posts: 11949
|
Posted: Wed Jun 09, 2010 10:54 am Post subject: |
|
narf wrote: | I can also reproduce this behavior in my Go-oo installation but not in OpenOffice.org 3.1.1. That means that one or more of the OpenOffice.org code modications made by Novell's Go-oo engineers is causing this bug. |
I can reproduce this bug as well so I will start investigating the Calc code to see if I can identify which Go-oo code modification needs to be removed to fix this bug.
I will post again when I have some news to report.
Patrick |
|
Back to top |
|
|
valterb The Anomaly (earlier version)
Joined: Sep 23, 2005 Posts: 463 Location: San Giuliano Terme, Pisa, Italy
|
Posted: Thu Jun 10, 2010 11:35 pm Post subject: |
|
I'm sure you know, but patch-test-12 doesn't resolve what reported here. |
|
Back to top |
|
|
pluby The Architect
Joined: Jun 16, 2003 Posts: 11949
|
Posted: Thu Jun 10, 2010 11:46 pm Post subject: |
|
valterb wrote: | I'm sure you know, but patch-test-12 doesn't resolve what reported here. |
Please have patience. It took most of today to fix the other bug. Fixing this bug will likely require several days of removing each Go-oo modification and recompiling one at time in order to find the modification that caused the bug.
Patrick |
|
Back to top |
|
|
valterb The Anomaly (earlier version)
Joined: Sep 23, 2005 Posts: 463 Location: San Giuliano Terme, Pisa, Italy
|
Posted: Fri Jun 11, 2010 4:18 am Post subject: |
|
Sorry. My fault. |
|
Back to top |
|
|
pluby The Architect
Joined: Jun 16, 2003 Posts: 11949
|
|
Back to top |
|
|
valterb The Anomaly (earlier version)
Joined: Sep 23, 2005 Posts: 463 Location: San Giuliano Terme, Pisa, Italy
|
Posted: Sat Jun 12, 2010 2:47 pm Post subject: |
|
Excellent. It works!
I really don't know another company that works like you do: in a few days you are able to resolve any kind of problem on this software. Really great! As always. |
|
Back to top |
|
|
pluby The Architect
Joined: Jun 16, 2003 Posts: 11949
|
Posted: Mon Jun 14, 2010 10:48 am Post subject: |
|
FYI. The fix for this bug is included in NeoOffice 3.1.1 Patch 1. You can download the patch from the NeoOffice patch download page.
Patrick |
|
Back to top |
|
|
valterb The Anomaly (earlier version)
Joined: Sep 23, 2005 Posts: 463 Location: San Giuliano Terme, Pisa, Italy
|
Posted: Mon Jun 14, 2010 3:11 pm Post subject: |
|
Patrick, I apologize so much, but I think I've found another misbehaviour.
I attach another sample here.
The fact is that if I choose to change an entryin coloum 2 (i.e. if I choose the wrong surname) the new entry brings to correct data only if the surname I choose is listed below any other surname I choose before. Let me explain this in a better manner.
I've worked on the original file (my original idea). Now you will find that the sheet that has to be filled is sheet1; data for the autofill is in sheet2 (I have a list also in sheet3, but that is not used at the moment). Let's say that surnames are listed like this:
- Ballantine
- Corey
- Damsey
- Finley
- Johnson
- Luby
(list hasn't to be alfabetically ordered)
Let's say you first choose Corey 8all the autofill works correctly), than Finley (again autofill works) but at this point you realize that the second should be Damsey; well, this doesn't work! But if you choose Luby everything works again.
[/list] |
|
Back to top |
|
|
pluby The Architect
Joined: Jun 16, 2003 Posts: 11949
|
Posted: Mon Jun 14, 2010 4:11 pm Post subject: |
|
I can reproduce what you see. Selecting some names in your sample file (like "BALDELLI") do not cause the other columns to recalculate.
I suspect that my fix in NeoOffice 3.1.1 Patch 1 needs to be tweaked to catch these new cases of the same bug.
I will investigate further and see if I can find a fix. I will post again when I have some news to report.
Patrick |
|
Back to top |
|
|
pluby The Architect
Joined: Jun 16, 2003 Posts: 11949
|
Posted: Mon Jun 14, 2010 4:48 pm Post subject: |
|
I have found the cause of the problem that you see and it is not a bug. Instead, the MATCH() function behaves in ways that you do not expect. Basically, the problem is that the MATCH() function takes 3 parameters. Since you have only put 2 parameters in each MATCH() function, NeoOffice's underlying OpenOffice.org code will use a default value of "1" for the missing parameter.
According to the NeoOffice help entry for the MATCH() function, when "1" is the last parameter, it is assumed that the first column of the search array is sorted in ascending order. The problem is that this is not true in your case. In your case, you have included the column headings (B1 through B3 on the second sheet) as the lookup cell range.
To get the MATCH() function to work the way you expect, you need to do one of the following changes to all of the MATCH() functions in your document:
1. Change the lookup cell range in each MATCH() function to start at B4 on the second sheet
2. Add "0" (exact match) as the third parameter in each MATCH() function
Hope that helps.
Patrick |
|
Back to top |
|
|
valterb The Anomaly (earlier version)
Joined: Sep 23, 2005 Posts: 463 Location: San Giuliano Terme, Pisa, Italy
|
Posted: Mon Jun 14, 2010 8:51 pm Post subject: |
|
That helps. Thanks. |
|
Back to top |
|
|
pluby The Architect
Joined: Jun 16, 2003 Posts: 11949
|
|
Back to top |
|
|
|