OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Formula only updates answer on reopen.
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
silky
General User
General User


Joined: 11 Sep 2010
Posts: 6

PostPosted: Sat Sep 11, 2010 3:18 pm    Post subject: Formula only updates answer on reopen. Reply with quote

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.
Back to top
View user's profile Send private message
therabi
Super User
Super User


Joined: 01 Sep 2010
Posts: 562

PostPosted: Sat Sep 11, 2010 4:13 pm    Post subject: Re: Formula only updates answer on reopen. Reply with quote

silky wrote:
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.


I am not sure this will help but check Tools> Cell Contents , is AutoCalculate checked? What happens if you press F9 to Recalculate?

If those do not help you check Tools> Options in the widow that pops-up check OpenOffice.org Calc> General, on the right hand side under Updating, see if "Always" will help.

HTH
_________________
OOO v3.3.0 & LO v3.4beta on Ubuntu 10.10 and Win7
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
stephaniem
Power User
Power User


Joined: 15 Oct 2007
Posts: 87

PostPosted: Sun Sep 12, 2010 6:05 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
silky
General User
General User


Joined: 11 Sep 2010
Posts: 6

PostPosted: Sun Sep 12, 2010 12:34 pm    Post subject: Reply with quote

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

Jamie.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Sep 12, 2010 1:25 pm    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
silky
General User
General User


Joined: 11 Sep 2010
Posts: 6

PostPosted: Mon Sep 13, 2010 12:30 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Sep 13, 2010 6:44 am    Post subject: Reply with quote

Rolling Eyes Rolling Eyes Rolling Eyes
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 https://forum.openoffice.org
Back to top
View user's profile Send private message
silky
General User
General User


Joined: 11 Sep 2010
Posts: 6

PostPosted: Tue Sep 14, 2010 12:08 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
silky
General User
General User


Joined: 11 Sep 2010
Posts: 6

PostPosted: Fri Sep 17, 2010 2:00 am    Post subject: Reply with quote

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

Any other ideas to try?

Jamie.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Sep 20, 2010 10:31 am    Post subject: Reply with quote

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

Any other ideas to try?

Jamie.

Edit>Links>Update Question
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
tmijs789
General User
General User


Joined: 14 May 2009
Posts: 23

PostPosted: Mon Sep 20, 2010 10:37 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Sep 20, 2010 11:08 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
tmijs789
General User
General User


Joined: 14 May 2009
Posts: 23

PostPosted: Mon Sep 20, 2010 11:36 am    Post subject: Reply with quote

Villeroy wrote:
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.


Yes this has been very handy. All outside machines are setup to say "never" to data updates as they would replace all the linked data with errors. This way we can send a file home with someone and they can keep working with an old copy of the data.

I am hopefully not far from re-creating the update issue and hopefully I can find out why sometimes it does not update on opening "sometimes". Both the document and the linked document are on a network location so that's my first 'guess' but I'd like to establish a frequency of issues before trying to isolate it with local files.
Back to top
View user's profile Send private message
tmijs789
General User
General User


Joined: 14 May 2009
Posts: 23

PostPosted: Mon Sep 20, 2010 1:24 pm    Post subject: Reply with quote

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?!
Back to top
View user's profile Send private message
tmijs789
General User
General User


Joined: 14 May 2009
Posts: 23

PostPosted: Mon Sep 20, 2010 3:29 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group