Re: [libreoffice-users] Re: Am I the only one with Err 522 problems?

2013/4/24 Alex Thurgood <alex.thurgood@gmail.com>:
> Le 02/04/2013 19:48, Johnny Rosenberg a écrit :
>
> Hi Johnny,
> Just a few thoughts in an attempt to narrow down the problem :
>
> - which version of OpenOffice.org did you originally use to create the file
> ?

I am not sure, but I have four of these files, one for each year (one
year is about 3000 rows in average and the whole thing were just too
slow for using the same file for many years), so the first one was
made in 2010, and I think it was LibreOffice, but I'm not sure. If
LibreOffice, it must have been one of the first versions, which one
was that? 3.2?

> - the cells that show Err 522, do they have a particular formula that
> requires recalculation ?

I'll open it and have a look…
Okay, it's open and now I can see that all my formatting of column A
is… gone?? Thank you, LibreOffice, or maybe Apache OpenOffice? I
fiddled with the file with both suites yesterday, but I think I used
LibreOffice last time I opened it…
I'll open it with Apache OpenOffice… Done. Column A formatted OK. Strange…

I know I have had that problem before with LibreOffice, that some
formatting (conditional formatting) are suddenly gone, so I guess I
should stay with Apache OpenOffice for at least that file…

Anyway, back to your question…
The first cell with Err:522 when I open the file now, is G320. The
formula is (translated to English):
=IF($E320="";"";IF($B320=G$1;$C320+G319+STYLE("Time");IF(INTEGER($A320)>INTEGER($A319);0+STYLE("Hide");G319+STYLE("Hide"))))
    ”Hide” is a style that hides the text with white characters and
white background. It just make it look a lot better and easier to
follow.
    ”Time” is for displaying time correctly, in this case H:MM:SS. All
other settings are the same as the default style, I think.

All rows looks the same (except maybe the first one), here are the
rest of its columns, in this case one row 320:
A320: 2013-02-07 11:40:33
    Just date and time following ISO-8601, which is very close to the
Swedish date- and time format.
B320: =WORK(A320;B319)
    WORK looks like this:

REM ***** Basic *****
Option Explicit

Function Work(CurrentTime As Date,PrevWork As String) As String
    If CurrentTime=0 Then
        Work=""
    ElseIf TimeValue(CurrentTime)<TimeValue("07:00:00") And _
        PrevWork<>"Arrival" Then
        Work="Arrival"
    ElseIf TimeValue(CurrentTime)>TimeValue("10:40:00") And _
    TimeValue(CurrentTime)<TimeValue("11:50:00") And _
    PrevWork<>"Lunch" Then
        Work="Lunch"
    ElseIf TimeValue(CurrentTime)>TimeValue("15:40:00") Then
        Work="Going home"
    ElseIf PrevWork<>"Transport" Then
        Work="Transport"
    Else
        Work="Enter customer, supplier or place"
    EndIf
End Function
REM ***** End of code *****

C320: =IF(OR(A321=0;A321<A320);"";IF(INTEGER(A321)>INTEGER(A320);"";A321-A320))
D320: <Empty>
    The D column is for comments only, so most of the rows are empty,
the rest are pure text, no formulas.
E320: =IF(E319="";C320;IF(OR(DAY(A321)>DAY(A320);C320="");"";IF(OR(B320="Lunch";B320="Doctor's
appointment");E319;C320+E319)))
F320: 
=IF($E320="";"";IF($B320=F$1;$C320+F319+STYLE("Time");IF(INTEGER($A320)>INTEGER($A319);0+STYLE("Hide");F319+STYLE("Hide"))))
    And finally, the already mentioned formula, in which the first
Err:522 occurs this time (not the same cell every time I open the
document):
G320: 
=IF($E320="";"";IF($B320=G$1;$C320+G319+STYLE("Time");IF(INTEGER($A320)>INTEGER($A319);0+STYLE("Hide");G319+STYLE("Hide"))))

Now, I select G320, Ctrl+ Ctrl+v. Poff – all error messages are gone…
(and replaced with the expected values).

Well, that's probably just about it.


> - if there is a formula in these cells, does it involve calculating strings
> with en empty, NULL or Zero value ?

I try to prevent that with the IF(SomeCell="";"";DoSomething), see the
formulas above. I think I covered all possible situations, but I'm not
100 % sure, of course.

> Alex

Thank you, for trying to help.


Johnny Rosenberg

> --
> For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
> Problems?
> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/global/users/
> All messages sent to this list will be publicly archived and cannot be
> deleted

-- 
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted