Formula only updates answer on reopen.

silky · General User Joined: 11 Sep 2010 Posts: 6

Hello,

Sorry new to this, have tried the search but can't find the answer.

Have a spreadsheet thats taking a sum from another spreadsheet, something like dailysalesE24.

Sorry problems at work and I'm on the home pc, so can't give correct formula, but the problem is the formula is correct, but the figures do not update unless we save the file, close it and then reopen.

Please help must be a simple setting somewhere that I can not find.

therabi · Super User Joined: 01 Sep 2010 Posts: 562

stephaniem · Power User Joined: 15 Oct 2007 Posts: 87

If the autocalculate is not activated, then there may be something wrong with your user profile.

See this for more information:
http://openoffice.ozlady.com/2010/06/user-profiles-in-openoffice-fixing-weird-calculations/
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
OOo 3.3.0 and NeoO 3.1.2 Patch 4 on Mac OS X 10.6.6
OOo 3.2.1 on Windows Vista
http://www.guide2office.com

silky · General User Joined: 11 Sep 2010 Posts: 6

Many thanks I will give it all a go tomorrow and get back to you.

Jamie.

Villeroy · Posted: Sun Sep 12, 2010 1:25 pm Post subject:

Before you destroy all your customizations: Why don't you tell us the formula you are writing about all the time?
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

silky · General User Joined: 11 Sep 2010 Posts: 6

Now that I'm back in work heres the formula in question.

Worked fine in execel for about 5 years now moved over to openoffice and no good so far.

Have tried all of therbai's ideas and still no joy.

=SUM('file:///C:/Users/First Choice Beds/Documents/Daily Excel/End of Day 05 17.09.10.xls'#$Friday.M34)

End of day 05 cell Friday.M34 is showing the correct figure, but spreadsheet with the above formula only shows correct figure on reopen.

Update:

Have now saved files as odf format and changed formula extension as well. Have checked settings described as above.

Still need help please.

Jamie.

Villeroy · Posted: Mon Sep 13, 2010 6:44 am Post subject:

You use external references in a xls and do not tell us this important fact.
No, this type of link can not update automatically. It keeps on working with cached data when you move the file to aother machine.
[Tutorial] External Links In Calc
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

silky · General User Joined: 11 Sep 2010 Posts: 6

Sorry thought I said that the information was coming from another spreadsheet.

Not sure that the info would be considered external? as it is stored on the same computer and even in the same folder as the other spreadsheet. Excel had no problem with it what so ever.

Many thanks for the link I'll have a play and post back my findings.

Jamie.

silky · General User Joined: 11 Sep 2010 Posts: 6

Tried all sorts of things and still cannot get this to update without reopening spreadsheet.

Any other ideas to try?

Jamie.

Villeroy · Posted: Mon Sep 20, 2010 10:31 am Post subject:

tmijs789 · General User Joined: 14 May 2009 Posts: 23

OO3.2 on Win7 Pro64, using Calc with multiple .ODS files:

I have the Tools->Options->OOO Calc->Gneral->"Updating" section set to "Always" but I have on multiple occasions noted that the data is NOT updated when I close and re-open.

If I test it, I can't make it fail to update, but I've seen old data still in linked documents long after the linked data was updated and it remained after open/close and ONLY updated when I went to Edit->Links->Update ?

I'll keep playing with it to see if I can narrow it down to a cause, but if there was a simple "on open" macro that would trigger an update, that would be "something" I can say was done to prevent this going forward.

Thanks!
_________________
If Necessity is the mother of invention, then Desperation must be the father!
providing business solutions ..... Metrofax

Villeroy · Posted: Mon Sep 20, 2010 11:08 am Post subject:

Read the tutorial. The data are carried with the target document so the links keep on working when the document is used on another machine.
When you update the link successfully from the source file, the data are read from the source file as it has been saved to the hard disk. Modifications in the source document need to be saved to disk.
If this is a problem, you may prefer the DDE links.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

tmijs789 · General User Joined: 14 May 2009 Posts: 23

tmijs789 · General User Joined: 14 May 2009 Posts: 23

Wait a moment.. DDE links are working now.. Interesting!

I might just migrate to DDE links for all future docs if that's potentially better?

I love the speed of the updates with DDE, if only it could be two-way?!

tmijs789 · General User Joined: 14 May 2009 Posts: 23

Ahhh that didn't take very long!

What I did..

Opened the reference/source doc, updated it, saved it, but didn't close it.

Opened the linked document and observed that the changes were updated.

Made some edits to the linked document and realized that it needed to be retired. So I did a "save-as" to an "old" sub-folder to retire the document. I did not close it.

Opened my master template, (without doing an Edit->Links->Update) and did a save-as over the original linked document, with that document still open but saved to the "old" sub-folder. I did not close this document either.

Then I copied the outline of the document (it's the first sheet) over to the replacement doc (our master template) and pasted (normal) to it. That pastes in linked fields and 1 of the 3 linked fields came in as "-" but the others were all linking and showing the data right. The document I copied from had the right data showing, but I still get a "-" on the freshly opened/saved document.

I went to Edit->Links->Update , and the "-" magically changed to the same value as in the source/reference doc, and now matched with the "retired" document's outline.

Does any of that detail help at all?