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

Hi.
On 2013-04-25 04:51, Johnny Rosenberg wrote:
> 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
I am sure you have tried, but did F9  or Shift+Ctrl+F9 do the trick. A 
quick search shows some discussion on failure to recalculate.
Steve

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